
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="zh_Hans">
  <head>
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>查询表达式 &#8212; Django 3.2.6.dev 文档</title>
    <link rel="stylesheet" href="../../_static/default.css" type="text/css" />
    <link rel="stylesheet" href="../../_static/pygments.css" type="text/css" />
    <script type="text/javascript" id="documentation_options" data-url_root="../../" src="../../_static/documentation_options.js"></script>
    <script type="text/javascript" src="../../_static/jquery.js"></script>
    <script type="text/javascript" src="../../_static/underscore.js"></script>
    <script type="text/javascript" src="../../_static/doctools.js"></script>
    <script type="text/javascript" src="../../_static/language_data.js"></script>
    <link rel="index" title="索引" href="../../genindex.html" />
    <link rel="search" title="搜索" href="../../search.html" />
    <link rel="next" title="条件表达式" href="conditional-expressions.html" />
    <link rel="prev" title="查找 API 参考" href="lookups.html" />



 
<script src="../../templatebuiltins.js"></script>
<script>
(function($) {
    if (!django_template_builtins) {
       // templatebuiltins.js missing, do nothing.
       return;
    }
    $(document).ready(function() {
        // Hyperlink Django template tags and filters
        var base = "../templates/builtins.html";
        if (base == "#") {
            // Special case for builtins.html itself
            base = "";
        }
        // Tags are keywords, class '.k'
        $("div.highlight\\-html\\+django span.k").each(function(i, elem) {
             var tagname = $(elem).text();
             if ($.inArray(tagname, django_template_builtins.ttags) != -1) {
                 var fragment = tagname.replace(/_/, '-');
                 $(elem).html("<a href='" + base + "#" + fragment + "'>" + tagname + "</a>");
             }
        });
        // Filters are functions, class '.nf'
        $("div.highlight\\-html\\+django span.nf").each(function(i, elem) {
             var filtername = $(elem).text();
             if ($.inArray(filtername, django_template_builtins.tfilters) != -1) {
                 var fragment = filtername.replace(/_/, '-');
                 $(elem).html("<a href='" + base + "#" + fragment + "'>" + filtername + "</a>");
             }
        });
    });
})(jQuery);</script>

  </head><body>

    <div class="document">
  <div id="custom-doc" class="yui-t6">
    <div id="hd">
      <h1><a href="../../index.html">Django 3.2.6.dev 文档</a></h1>
      <div id="global-nav">
        <a title="Home page" href="../../index.html">Home</a>  |
        <a title="Table of contents" href="../../contents.html">Table of contents</a>  |
        <a title="Global index" href="../../genindex.html">Index</a>  |
        <a title="Module index" href="../../py-modindex.html">Modules</a>
      </div>
      <div class="nav">
    &laquo; <a href="lookups.html" title="查找 API 参考">previous</a>
     |
    <a href="../index.html" title="API 参考" accesskey="U">up</a>
   |
    <a href="conditional-expressions.html" title="条件表达式">next</a> &raquo;</div>
    </div>

    <div id="bd">
      <div id="yui-main">
        <div class="yui-b">
          <div class="yui-g" id="ref-models-expressions">
            
  <div class="section" id="s-query-expressions">
<span id="query-expressions"></span><h1>查询表达式<a class="headerlink" href="#query-expressions" title="永久链接至标题">¶</a></h1>
<p>查询表达式描述了一个值或一个计算，它可以作为更新、创建、过滤、排序、注解或聚合的一部分。当一个表达式输出一个布尔值时，它可以直接用于过滤器中。有许多内置的表达式（在下面的文档中）可以用来帮助你编写查询。表达式可以组合，或者在某些情况下嵌套，以形成更复杂的计算。</p>
<div class="section" id="s-supported-arithmetic">
<span id="supported-arithmetic"></span><h2>支持的算术<a class="headerlink" href="#supported-arithmetic" title="永久链接至标题">¶</a></h2>
<p>Django 支持负、加、减、乘、除、模数运算，以及对查询表达式的幂运算符，使用 Python 常量、变量，甚至其他表达式。</p>
</div>
<div class="section" id="s-some-examples">
<span id="some-examples"></span><h2>一些例子<a class="headerlink" href="#some-examples" title="永久链接至标题">¶</a></h2>
<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Count</span><span class="p">,</span> <span class="n">F</span><span class="p">,</span> <span class="n">Value</span>
<span class="kn">from</span> <span class="nn">django.db.models.functions</span> <span class="kn">import</span> <span class="n">Length</span><span class="p">,</span> <span class="n">Upper</span>

<span class="c1"># Find companies that have more employees than chairs.</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">num_employees__gt</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_chairs&#39;</span><span class="p">))</span>

<span class="c1"># Find companies that have at least twice as many employees</span>
<span class="c1"># as chairs. Both the querysets below are equivalent.</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">num_employees__gt</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_chairs&#39;</span><span class="p">)</span> <span class="o">*</span> <span class="mi">2</span><span class="p">)</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
    <span class="n">num_employees__gt</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_chairs&#39;</span><span class="p">)</span> <span class="o">+</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_chairs&#39;</span><span class="p">))</span>

<span class="c1"># How many chairs are needed for each company to seat all employees?</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">company</span> <span class="o">=</span> <span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="o">...</span>    <span class="n">num_employees__gt</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_chairs&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
<span class="o">...</span>    <span class="n">chairs_needed</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_employees&#39;</span><span class="p">)</span> <span class="o">-</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_chairs&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">company</span><span class="o">.</span><span class="n">num_employees</span>
<span class="mi">120</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">company</span><span class="o">.</span><span class="n">num_chairs</span>
<span class="mi">50</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">company</span><span class="o">.</span><span class="n">chairs_needed</span>
<span class="mi">70</span>

<span class="c1"># Create a new company using expressions.</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">company</span> <span class="o">=</span> <span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">create</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">&#39;Google&#39;</span><span class="p">,</span> <span class="n">ticker</span><span class="o">=</span><span class="n">Upper</span><span class="p">(</span><span class="n">Value</span><span class="p">(</span><span class="s1">&#39;goog&#39;</span><span class="p">)))</span>
<span class="c1"># Be sure to refresh it if you need to access the field.</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">company</span><span class="o">.</span><span class="n">refresh_from_db</span><span class="p">()</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">company</span><span class="o">.</span><span class="n">ticker</span>
<span class="s1">&#39;GOOG&#39;</span>

<span class="c1"># Annotate models with an aggregated value. Both forms</span>
<span class="c1"># below are equivalent.</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_products</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="s1">&#39;products&#39;</span><span class="p">))</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_products</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;products&#39;</span><span class="p">)))</span>

<span class="c1"># Aggregates can contain complex computations also</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">num_offerings</span><span class="o">=</span><span class="n">Count</span><span class="p">(</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;products&#39;</span><span class="p">)</span> <span class="o">+</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;services&#39;</span><span class="p">)))</span>

<span class="c1"># Expressions can also be used in order_by(), either directly</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">Length</span><span class="p">(</span><span class="s1">&#39;name&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">asc</span><span class="p">())</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">Length</span><span class="p">(</span><span class="s1">&#39;name&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span>
<span class="c1"># or using the double underscore lookup syntax.</span>
<span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">CharField</span>
<span class="kn">from</span> <span class="nn">django.db.models.functions</span> <span class="kn">import</span> <span class="n">Length</span>
<span class="n">CharField</span><span class="o">.</span><span class="n">register_lookup</span><span class="p">(</span><span class="n">Length</span><span class="p">)</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s1">&#39;name__length&#39;</span><span class="p">)</span>

<span class="c1"># Boolean expression can be used directly in filters.</span>
<span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Exists</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
    <span class="n">Exists</span><span class="p">(</span><span class="n">Employee</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">company</span><span class="o">=</span><span class="n">OuterRef</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">),</span> <span class="n">salary__gt</span><span class="o">=</span><span class="mi">10</span><span class="p">))</span>
<span class="p">)</span>
</pre></div>
</div>
</div>
<div class="section" id="s-built-in-expressions">
<span id="built-in-expressions"></span><h2>内置表达式<a class="headerlink" href="#built-in-expressions" title="永久链接至标题">¶</a></h2>
<div class="admonition note">
<p class="first admonition-title">注解</p>
<p class="last">这些表达式在 <code class="docutils literal notranslate"><span class="pre">django.db.models.expressions</span></code> 和 <code class="docutils literal notranslate"><span class="pre">django.db.models.aggregates</span></code> 中定义，但为了方便起见，通常从 <a class="reference internal" href="../../topics/db/models.html#module-django.db.models" title="django.db.models"><code class="xref py py-mod docutils literal notranslate"><span class="pre">django.db.models</span></code></a> 中导入使用。</p>
</div>
<div class="section" id="s-f-expressions">
<span id="f-expressions"></span><h3><code class="docutils literal notranslate"><span class="pre">F()</span></code> 表达式<a class="headerlink" href="#f-expressions" title="永久链接至标题">¶</a></h3>
<dl class="class">
<dt id="django.db.models.F">
<em class="property">class </em><code class="descname">F</code><a class="headerlink" href="#django.db.models.F" title="永久链接至目标">¶</a></dt>
<dd></dd></dl>

<p>An <code class="docutils literal notranslate"><span class="pre">F()</span></code> object represents the value of a model field, transformed value of a
model field, or annotated column. It makes it possible to refer to model field
values and perform database operations using them without actually having to
pull them out of the database into Python memory.</p>
<p>取而代之的是，Django 使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 对象来生成一个 SQL 表达式，在数据库层面描述所需的操作。</p>
<p>我们举个例子试试。通常情况下，我们可以这样做：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># Tintin filed a news story!</span>
<span class="n">reporter</span> <span class="o">=</span> <span class="n">Reporters</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">&#39;Tintin&#39;</span><span class="p">)</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">stories_filed</span> <span class="o">+=</span> <span class="mi">1</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">save</span><span class="p">()</span>
</pre></div>
</div>
<p>这里，我们从数据库中提取了 <code class="docutils literal notranslate"><span class="pre">reporter.stories_filed</span></code> 的值到内存中，并使用熟悉的 Python 操作符对其进行操作，然后将对象保存回数据库。但我们也可以这样做：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">F</span>

<span class="n">reporter</span> <span class="o">=</span> <span class="n">Reporters</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">&#39;Tintin&#39;</span><span class="p">)</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">stories_filed</span> <span class="o">=</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;stories_filed&#39;</span><span class="p">)</span> <span class="o">+</span> <span class="mi">1</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">save</span><span class="p">()</span>
</pre></div>
</div>
<p>虽然 <code class="docutils literal notranslate"><span class="pre">reporter.stories_filed</span> <span class="pre">=</span> <span class="pre">F('stories_filed')</span> <span class="pre">+</span> <span class="pre">1</span></code> 看起来像一个普通的 Python 赋值给一个实例属性，但实际上它是一个描述数据库操作的 SQL 结构。</p>
<p>当 Django 遇到 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 的实例时，它会覆盖标准的 Python 运算符来创建一个封装的 SQL 表达式；在本例中，它指示数据库递增由 <code class="docutils literal notranslate"><span class="pre">reporter.stories_filed</span></code> 表示的数据库字段。</p>
<p>无论 <code class="docutils literal notranslate"><span class="pre">reporter.stories_filed</span></code> 上的值是多少，Python 永远不会知道它——它完全由数据库处理。通过 Django 的 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 类，Python 所做的就是创建 SQL 语法来引用这个字段并描述操作。</p>
<p>要访问这样保存的新值，必须重新加载对象：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">reporter</span> <span class="o">=</span> <span class="n">Reporters</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">pk</span><span class="o">=</span><span class="n">reporter</span><span class="o">.</span><span class="n">pk</span><span class="p">)</span>
<span class="c1"># Or, more succinctly:</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">refresh_from_db</span><span class="p">()</span>
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">F()</span></code> 除了用于上述对单个实例的操作外，<code class="docutils literal notranslate"><span class="pre">F()</span></code> 还可以与 <code class="docutils literal notranslate"><span class="pre">update()</span></code> 一起用于对象实例的 <code class="docutils literal notranslate"><span class="pre">QuerySets</span></code>。这就把我们上面使用的两个查询——<code class="docutils literal notranslate"><span class="pre">get()</span></code> 和 <a class="reference internal" href="instances.html#django.db.models.Model.save" title="django.db.models.Model.save"><code class="xref py py-meth docutils literal notranslate"><span class="pre">save()</span></code></a> 减少到只有一个：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">reporter</span> <span class="o">=</span> <span class="n">Reporters</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">&#39;Tintin&#39;</span><span class="p">)</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">stories_filed</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;stories_filed&#39;</span><span class="p">)</span> <span class="o">+</span> <span class="mi">1</span><span class="p">)</span>
</pre></div>
</div>
<p>我们还可以使用 <a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.update" title="django.db.models.query.QuerySet.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">update()</span></code></a> 来递增多个对象上的字段值——这可能比从数据库中把它们全部拉到 Python 中，在它们身上循环，递增每个对象的字段值，然后把每个对象保存回数据库要快得多：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Reporter</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">all</span><span class="p">()</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">stories_filed</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;stories_filed&#39;</span><span class="p">)</span> <span class="o">+</span> <span class="mi">1</span><span class="p">)</span>
</pre></div>
</div>
<p>因此，<code class="docutils literal notranslate"><span class="pre">F()</span></code> 可以通过以下方式提供性能优势：</p>
<ul class="simple">
<li>让数据库，而不是 Python 来完成工作</li>
<li>减少某些操作所需的查询次数</li>
</ul>
<div class="versionchanged">
<span class="title">Changed in Django 3.2:</span> <p>Support for transforms of the field was added.</p>
</div>
<div class="section" id="s-avoiding-race-conditions-using-f">
<span id="s-id1"></span><span id="avoiding-race-conditions-using-f"></span><span id="id1"></span><h4>使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 避免竞争条件<a class="headerlink" href="#avoiding-race-conditions-using-f" title="永久链接至标题">¶</a></h4>
<p><code class="docutils literal notranslate"><span class="pre">F()</span></code> 的另一个有用的好处是，让数据库——而不是 Python——更新一个字段的值，避免了 <em>竞争条件</em>。</p>
<p>如果两个 Python 线程执行上面第一个例子中的代码，一个线程可以在另一个线程从数据库中获取一个字段的值后，检索、递增并保存它。第二个线程保存的值将基于原始值，第一个线程的工作将丢失。</p>
<p>如果数据库负责更新字段，那么这个过程就比较稳健：它只会在执行 <a class="reference internal" href="instances.html#django.db.models.Model.save" title="django.db.models.Model.save"><code class="xref py py-meth docutils literal notranslate"><span class="pre">save()</span></code></a> 或 <code class="docutils literal notranslate"><span class="pre">update()</span></code> 时，根据数据库中字段的值来更新字段，而不是根据检索实例时的值来更新。</p>
</div>
<div class="section" id="s-f-assignments-persist-after-model-save">
<span id="f-assignments-persist-after-model-save"></span><h4><code class="docutils literal notranslate"><span class="pre">F()</span></code> 赋值在 <code class="docutils literal notranslate"><span class="pre">Model.save()</span></code> 之后持续存在<a class="headerlink" href="#f-assignments-persist-after-model-save" title="永久链接至标题">¶</a></h4>
<p><code class="docutils literal notranslate"><span class="pre">F()</span></code> 分配给模型字段的对象在保存模型实例后会持续存在，并将应用于每个 <a class="reference internal" href="instances.html#django.db.models.Model.save" title="django.db.models.Model.save"><code class="xref py py-meth docutils literal notranslate"><span class="pre">save()</span></code></a>。例如：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">reporter</span> <span class="o">=</span> <span class="n">Reporters</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">&#39;Tintin&#39;</span><span class="p">)</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">stories_filed</span> <span class="o">=</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;stories_filed&#39;</span><span class="p">)</span> <span class="o">+</span> <span class="mi">1</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">save</span><span class="p">()</span>

<span class="n">reporter</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;Tintin Jr.&#39;</span>
<span class="n">reporter</span><span class="o">.</span><span class="n">save</span><span class="p">()</span>
</pre></div>
</div>
<p>在这种情况下，<code class="docutils literal notranslate"><span class="pre">stories_filed</span></code> 将被更新两次。如果最初是 <code class="docutils literal notranslate"><span class="pre">1</span></code>，最终值将是 <code class="docutils literal notranslate"><span class="pre">3</span></code>。这种持久性可以通过在保存模型对象后重新加载来避免，例如，使用 <a class="reference internal" href="instances.html#django.db.models.Model.refresh_from_db" title="django.db.models.Model.refresh_from_db"><code class="xref py py-meth docutils literal notranslate"><span class="pre">refresh_from_db()</span></code></a>。</p>
</div>
<div class="section" id="s-using-f-in-filters">
<span id="using-f-in-filters"></span><h4>在过滤器中使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code><a class="headerlink" href="#using-f-in-filters" title="永久链接至标题">¶</a></h4>
<p><code class="docutils literal notranslate"><span class="pre">F()</span></code> 在 <code class="docutils literal notranslate"><span class="pre">QuerySet</span></code> 过滤器中也非常有用，它们可以根据对象的字段值而不是 Python 值的标准来过滤一组对象。</p>
<p>这在 <a class="reference internal" href="../../topics/db/queries.html#using-f-expressions-in-filters"><span class="std std-ref">在查询中使用 F() 表达式</span></a> 中有所记载。</p>
</div>
<div class="section" id="s-using-f-with-annotations">
<span id="s-id2"></span><span id="using-f-with-annotations"></span><span id="id2"></span><h4>与注解一起使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code><a class="headerlink" href="#using-f-with-annotations" title="永久链接至标题">¶</a></h4>
<p><code class="docutils literal notranslate"><span class="pre">F()</span></code> 可用于通过将不同的字段与算术相结合，在你的模型上创建动态字段：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">company</span> <span class="o">=</span> <span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
    <span class="n">chairs_needed</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_employees&#39;</span><span class="p">)</span> <span class="o">-</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;num_chairs&#39;</span><span class="p">))</span>
</pre></div>
</div>
<p>如果你要组合的字段是不同类型的，你需要告诉 Django 将返回什么样的字段。由于 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 不直接支持 <code class="docutils literal notranslate"><span class="pre">output_field</span></code>，你需要用 <a class="reference internal" href="#django.db.models.ExpressionWrapper" title="django.db.models.ExpressionWrapper"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExpressionWrapper</span></code></a> 来包装表达式：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">DateTimeField</span><span class="p">,</span> <span class="n">ExpressionWrapper</span><span class="p">,</span> <span class="n">F</span>

<span class="n">Ticket</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
    <span class="n">expires</span><span class="o">=</span><span class="n">ExpressionWrapper</span><span class="p">(</span>
        <span class="n">F</span><span class="p">(</span><span class="s1">&#39;active_at&#39;</span><span class="p">)</span> <span class="o">+</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;duration&#39;</span><span class="p">),</span> <span class="n">output_field</span><span class="o">=</span><span class="n">DateTimeField</span><span class="p">()))</span>
</pre></div>
</div>
<p>当引用关系字段如 <code class="docutils literal notranslate"><span class="pre">ForeignKey</span></code> 时，<code class="docutils literal notranslate"><span class="pre">F()</span></code> 返回主键值而不是模型实例：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">&gt;&gt;</span> <span class="n">car</span> <span class="o">=</span> <span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">built_by</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;manufacturer&#39;</span><span class="p">))[</span><span class="mi">0</span><span class="p">]</span>
<span class="o">&gt;&gt;</span> <span class="n">car</span><span class="o">.</span><span class="n">manufacturer</span>
<span class="o">&lt;</span><span class="n">Manufacturer</span><span class="p">:</span> <span class="n">Toyota</span><span class="o">&gt;</span>
<span class="o">&gt;&gt;</span> <span class="n">car</span><span class="o">.</span><span class="n">built_by</span>
<span class="mi">3</span>
</pre></div>
</div>
</div>
<div class="section" id="s-using-f-to-sort-null-values">
<span id="s-id3"></span><span id="using-f-to-sort-null-values"></span><span id="id3"></span><h4>使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 对空值进行排序<a class="headerlink" href="#using-f-to-sort-null-values" title="永久链接至标题">¶</a></h4>
<p>使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 和 <a class="reference internal" href="#django.db.models.Expression.asc" title="django.db.models.Expression.asc"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Expression.asc()</span></code></a> 或 <code class="xref py py-meth docutils literal notranslate"><span class="pre">esc()</span></code> 的关键词参数 <code class="docutils literal notranslate"><span class="pre">nulls_first</span></code> 或 <code class="docutils literal notranslate"><span class="pre">nulls_last</span></code> 来控制字段的空值的排序。默认情况下，排序取决于你的数据库。</p>
<p>例如，在已经联系过的公司之后，对尚未联系过的公司进行排序（<code class="docutils literal notranslate"><span class="pre">last_contacted</span></code> 为空）：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">F</span>
<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;last_contacted&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">desc</span><span class="p">(</span><span class="n">nulls_last</span><span class="o">=</span><span class="kc">True</span><span class="p">))</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="s-func-expressions">
<span id="s-id4"></span><span id="func-expressions"></span><span id="id4"></span><h3><code class="docutils literal notranslate"><span class="pre">Func()</span></code> 表达式<a class="headerlink" href="#func-expressions" title="永久链接至标题">¶</a></h3>
<p><code class="docutils literal notranslate"><span class="pre">Func()</span></code> 表达式是所有涉及 <code class="docutils literal notranslate"><span class="pre">COALESCE</span></code> 和 <code class="docutils literal notranslate"><span class="pre">LOWER</span></code> 等数据库函数或 <code class="docutils literal notranslate"><span class="pre">SUM</span></code> 等集合的表达式的基本类型。它们可以直接使用：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">F</span><span class="p">,</span> <span class="n">Func</span>

<span class="n">queryset</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">field_lower</span><span class="o">=</span><span class="n">Func</span><span class="p">(</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;field&#39;</span><span class="p">),</span> <span class="n">function</span><span class="o">=</span><span class="s1">&#39;LOWER&#39;</span><span class="p">))</span>
</pre></div>
</div>
<p>或者可以用它们来建立一个数据库函数库：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Lower</span><span class="p">(</span><span class="n">Func</span><span class="p">):</span>
    <span class="n">function</span> <span class="o">=</span> <span class="s1">&#39;LOWER&#39;</span>

<span class="n">queryset</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">field_lower</span><span class="o">=</span><span class="n">Lower</span><span class="p">(</span><span class="s1">&#39;field&#39;</span><span class="p">))</span>
</pre></div>
</div>
<p>但这两种情况都会产生一个查询集，其中每个模型都有一个额外的属性 <code class="docutils literal notranslate"><span class="pre">field_lower</span></code> 来注释，大致由以下 SQL 产生：</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span>
    <span class="p">...</span>
    <span class="k">LOWER</span><span class="p">(</span><span class="ss">&quot;db_table&quot;</span><span class="p">.</span><span class="ss">&quot;field&quot;</span><span class="p">)</span> <span class="k">as</span> <span class="ss">&quot;field_lower&quot;</span>
</pre></div>
</div>
<p>参见 <a class="reference internal" href="database-functions.html"><span class="doc">数据库函数</span></a> 获取内置的数据库函数列表。</p>
<p><code class="docutils literal notranslate"><span class="pre">Func</span></code> API 如下：</p>
<dl class="class">
<dt id="django.db.models.Func">
<em class="property">class </em><code class="descname">Func</code>(<em>*expressions</em>, <em>**extra</em>)<a class="headerlink" href="#django.db.models.Func" title="永久链接至目标">¶</a></dt>
<dd><dl class="attribute">
<dt id="django.db.models.Func.function">
<code class="descname">function</code><a class="headerlink" href="#django.db.models.Func.function" title="永久链接至目标">¶</a></dt>
<dd><p>描述将要生成的函数的类属性。具体来说，<code class="docutils literal notranslate"><span class="pre">function</span></code> 将作为 <code class="docutils literal notranslate"><span class="pre">function</span></code> 占位符插在 <a class="reference internal" href="#django.db.models.Func.template" title="django.db.models.Func.template"><code class="xref py py-attr docutils literal notranslate"><span class="pre">template</span></code></a> 中。默认值为 <code class="docutils literal notranslate"><span class="pre">None</span></code>。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Func.template">
<code class="descname">template</code><a class="headerlink" href="#django.db.models.Func.template" title="永久链接至目标">¶</a></dt>
<dd><p>一个类属性，作为格式字符串，描述为该函数生成的SQL。默认值为 <code class="docutils literal notranslate"><span class="pre">'%(function)s(%(expressions)s)'</span></code>。</p>
<p>如果你正在构造像 <code class="docutils literal notranslate"><span class="pre">strftime('%W',</span> <span class="pre">'date')</span></code> 这样的 SQL，并且需要在查询中使用 <code class="docutils literal notranslate"><span class="pre">%</span></code> 字符，那么在 <code class="docutils literal notranslate"><span class="pre">template</span></code> 属性中把它四倍（<code class="docutils literal notranslate"><span class="pre">%%%%</span></code>），因为这个字符串会被插值两次：一次是在 <code class="docutils literal notranslate"><span class="pre">as_sql()</span></code> 中的模板插值中，另一次是在数据库游标中的查询参数的 SQL 插值中。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Func.arg_joiner">
<code class="descname">arg_joiner</code><a class="headerlink" href="#django.db.models.Func.arg_joiner" title="永久链接至目标">¶</a></dt>
<dd><p>一个类属性，表示用于将 <code class="docutils literal notranslate"><span class="pre">expressions</span></code> 列表连接在一起的字符。默认值为 <code class="docutils literal notranslate"><span class="pre">',</span> <span class="pre">'</span></code>。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Func.arity">
<code class="descname">arity</code><a class="headerlink" href="#django.db.models.Func.arity" title="永久链接至目标">¶</a></dt>
<dd><p>一个类属性，表示函数接受的参数数。如果设置了这个属性，并且用不同数量的表达式调用函数，将引发 <code class="docutils literal notranslate"><span class="pre">TypeError</span></code>。默认值为 <code class="docutils literal notranslate"><span class="pre">None</span></code>。</p>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Func.as_sql">
<code class="descname">as_sql</code>(<em>compiler</em>, <em>connection</em>, <em>function=None</em>, <em>template=None</em>, <em>arg_joiner=None</em>, <em>**extra_context</em>)<a class="headerlink" href="#django.db.models.Func.as_sql" title="永久链接至目标">¶</a></dt>
<dd><p>生成数据库函数的 SQL 片段。返回一个元组 <code class="docutils literal notranslate"><span class="pre">(sql,</span> <span class="pre">params)</span></code>，其中 <code class="docutils literal notranslate"><span class="pre">sql</span></code> 是 SQL 字符串，<code class="docutils literal notranslate"><span class="pre">params</span></code> 是查询参数的列表或元组。</p>
<p><code class="docutils literal notranslate"><span class="pre">as_vendor()</span></code> 方法应该使用 <code class="docutils literal notranslate"><span class="pre">function</span></code>、<code class="docutils literal notranslate"><span class="pre">template</span></code>、<code class="docutils literal notranslate"><span class="pre">arg_joiner</span></code> 以及其他 <code class="docutils literal notranslate"><span class="pre">**extra_context</span></code> 参数来根据需要定制 SQL。例如：</p>
<div class="literal-block-wrapper docutils container" id="id5">
<div class="code-block-caption"><span class="caption-text">django/db/models/functions.py</span><a class="headerlink" href="#id5" title="永久链接至代码">¶</a></div>
<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">ConcatPair</span><span class="p">(</span><span class="n">Func</span><span class="p">):</span>
    <span class="o">...</span>
    <span class="n">function</span> <span class="o">=</span> <span class="s1">&#39;CONCAT&#39;</span>
    <span class="o">...</span>

    <span class="k">def</span> <span class="nf">as_mysql</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="o">**</span><span class="n">extra_context</span><span class="p">):</span>
        <span class="k">return</span> <span class="nb">super</span><span class="p">()</span><span class="o">.</span><span class="n">as_sql</span><span class="p">(</span>
            <span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span>
            <span class="n">function</span><span class="o">=</span><span class="s1">&#39;CONCAT_WS&#39;</span><span class="p">,</span>
            <span class="n">template</span><span class="o">=</span><span class="s2">&quot;</span><span class="si">%(function)s</span><span class="s2">(&#39;&#39;, </span><span class="si">%(expressions)s</span><span class="s2">)&quot;</span><span class="p">,</span>
            <span class="o">**</span><span class="n">extra_context</span>
        <span class="p">)</span>
</pre></div>
</div>
</div>
<p>为了避免 SQL 注入漏洞，<code class="docutils literal notranslate"><span class="pre">extra_context</span></code> <a class="reference internal" href="#avoiding-sql-injection-in-query-expressions"><span class="std std-ref">不得包含不受信任的用户输入</span></a>，因为这些值会被内插到 SQL 字符串中，而不是作为查询参数传递，数据库驱动对其进行转义。</p>
</dd></dl>

</dd></dl>

<p><code class="docutils literal notranslate"><span class="pre">*expressions</span></code> 参数是一个位置表达式的列表，该函数将应用于此。表达式将被转换为字符串，用 <code class="docutils literal notranslate"><span class="pre">arg_joiner</span></code> 连接在一起，然后插入到 <code class="docutils literal notranslate"><span class="pre">template</span></code> 中作为 <code class="docutils literal notranslate"><span class="pre">expressions</span></code> 占位符。</p>
<p>位置参数可以是表达式或 Python 值。字符串被认为是列引用，将被包装在 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 表达式中，而其他值将被包装在 <code class="docutils literal notranslate"><span class="pre">Value()</span></code> 表达式中。</p>
<p><code class="docutils literal notranslate"><span class="pre">**extra</span></code> 关键字是 <code class="docutils literal notranslate"><span class="pre">key=value</span></code> 对，可以内插到 <code class="docutils literal notranslate"><span class="pre">template</span></code> 属性中。为了避免 SQL 注入漏洞，<code class="docutils literal notranslate"><span class="pre">extra</span></code> <a class="reference internal" href="#avoiding-sql-injection-in-query-expressions"><span class="std std-ref">不能包含不受信任的用户输入</span></a>，因为这些值会被内插到 SQL 字符串中，而不是作为查询参数传递，数据库驱动程序对其进行转义。</p>
<p><code class="docutils literal notranslate"><span class="pre">function</span></code>、<code class="docutils literal notranslate"><span class="pre">template</span></code> 和 <code class="docutils literal notranslate"><span class="pre">arg_joiner</span></code> 关键字可以用来替换同名的属性，而不必定义自己的类。<code class="docutils literal notranslate"><span class="pre">output_field</span></code> 可以用来定义预期的返回类型。</p>
</div>
<div class="section" id="s-aggregate-expressions">
<span id="aggregate-expressions"></span><h3><code class="docutils literal notranslate"><span class="pre">Aggregate()</span></code> 表达式<a class="headerlink" href="#aggregate-expressions" title="永久链接至标题">¶</a></h3>
<p>聚合表达式是 <a class="reference internal" href="#func-expressions"><span class="std std-ref">Func() 表达式</span></a> 的一个特例，它通知查询需要一个 <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> 子句。所有的 <a class="reference internal" href="querysets.html#aggregation-functions"><span class="std std-ref">聚合函数</span></a>，如 <code class="docutils literal notranslate"><span class="pre">Sum()</span></code> 和 <code class="docutils literal notranslate"><span class="pre">Count()</span></code>，都继承自 <code class="docutils literal notranslate"><span class="pre">Aggregate()</span></code>。</p>
<p>由于 <code class="docutils literal notranslate"><span class="pre">Aggregate</span></code> 是表达式和封装表达式，你可以表示一些复杂的计算：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Count</span>

<span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
    <span class="n">managers_required</span><span class="o">=</span><span class="p">(</span><span class="n">Count</span><span class="p">(</span><span class="s1">&#39;num_employees&#39;</span><span class="p">)</span> <span class="o">/</span> <span class="mi">4</span><span class="p">)</span> <span class="o">+</span> <span class="n">Count</span><span class="p">(</span><span class="s1">&#39;num_managers&#39;</span><span class="p">))</span>
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">Aggregate</span></code> 的 API 如下：</p>
<dl class="class">
<dt id="django.db.models.Aggregate">
<em class="property">class </em><code class="descname">Aggregate</code>(<em>*expressions</em>, <em>output_field=None</em>, <em>distinct=False</em>, <em>filter=None</em>, <em>**extra</em>)<a class="headerlink" href="#django.db.models.Aggregate" title="永久链接至目标">¶</a></dt>
<dd><dl class="attribute">
<dt id="django.db.models.Aggregate.template">
<code class="descname">template</code><a class="headerlink" href="#django.db.models.Aggregate.template" title="永久链接至目标">¶</a></dt>
<dd><p>作为格式字符串的类属性，描述为该集合生成的 SQL。默认值为 <code class="docutils literal notranslate"><span class="pre">'%(function)s(%(distinct)s%(expressions)s)'</span></code>。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Aggregate.function">
<code class="descname">function</code><a class="headerlink" href="#django.db.models.Aggregate.function" title="永久链接至目标">¶</a></dt>
<dd><p>描述将生成的集合函数的类属性。具体来说，<code class="docutils literal notranslate"><span class="pre">function</span></code> 将作为 <code class="docutils literal notranslate"><span class="pre">function</span></code> 占位符插值在 <a class="reference internal" href="#django.db.models.Aggregate.template" title="django.db.models.Aggregate.template"><code class="xref py py-attr docutils literal notranslate"><span class="pre">template</span></code></a> 中。默认值为 <code class="docutils literal notranslate"><span class="pre">None</span></code>。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Aggregate.window_compatible">
<code class="descname">window_compatible</code><a class="headerlink" href="#django.db.models.Aggregate.window_compatible" title="永久链接至目标">¶</a></dt>
<dd><p>默认为 <code class="docutils literal notranslate"><span class="pre">True</span></code>，因为大多数聚合函数可以作为 <a class="reference internal" href="#django.db.models.expressions.Window" title="django.db.models.expressions.Window"><code class="xref py py-class docutils literal notranslate"><span class="pre">Window</span></code></a> 中的源表达式。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Aggregate.allow_distinct">
<code class="descname">allow_distinct</code><a class="headerlink" href="#django.db.models.Aggregate.allow_distinct" title="永久链接至目标">¶</a></dt>
<dd><p>一个类属性，决定该聚合函数是否允许传递 <code class="docutils literal notranslate"><span class="pre">distinct</span></code> 关键字参数。如果设置为 <code class="docutils literal notranslate"><span class="pre">False</span></code> （默认），如果传递了 <code class="docutils literal notranslate"><span class="pre">distinct=True</span></code>，会引发 <code class="docutils literal notranslate"><span class="pre">TypeError</span></code>。</p>
</dd></dl>

</dd></dl>

<p>The <code class="docutils literal notranslate"><span class="pre">expressions</span></code> positional arguments can include expressions, transforms of
the model field, or the names of model fields. They will be converted to a
string and used as the <code class="docutils literal notranslate"><span class="pre">expressions</span></code> placeholder within the <code class="docutils literal notranslate"><span class="pre">template</span></code>.</p>
<p><code class="docutils literal notranslate"><span class="pre">output_field</span></code> 参数需要一个模型字段实例，比如 <code class="docutils literal notranslate"><span class="pre">IntegerField()</span></code> 或 <code class="docutils literal notranslate"><span class="pre">BooleanField()</span></code>，Django 会在从数据库中获取值后将其载入其中。通常在实例化模型字段时，不需要任何参数，因为任何与数据验证有关的参数（<code class="docutils literal notranslate"><span class="pre">max_length</span></code>、<code class="docutils literal notranslate"><span class="pre">max_digits</span></code> 等）都不会在表达式的输出值上执行。</p>
<p>请注意，只有当 Django 无法确定结果的字段类型时，才需要使用 <code class="docutils literal notranslate"><span class="pre">output_field</span></code>。混合字段类型的复杂表达式应该定义所需的 <code class="docutils literal notranslate"><span class="pre">output_field</span></code>。例如，将一个 <code class="docutils literal notranslate"><span class="pre">IntegerField()</span></code> 和一个 <code class="docutils literal notranslate"><span class="pre">FloatField()</span></code> 加在一起，可能应该定义 <code class="docutils literal notranslate"><span class="pre">output_field=FloatField()</span></code>。</p>
<p><code class="docutils literal notranslate"><span class="pre">distinct</span></code> 参数决定是否应该为 <code class="docutils literal notranslate"><span class="pre">expressions</span></code> 的每一个不同的值（或一组值，对于多个 <code class="docutils literal notranslate"><span class="pre">expressions</span></code>）调用聚合函数。该参数仅在 <a class="reference internal" href="#django.db.models.Aggregate.allow_distinct" title="django.db.models.Aggregate.allow_distinct"><code class="xref py py-attr docutils literal notranslate"><span class="pre">allow_distinct</span></code></a> 设置为 <code class="docutils literal notranslate"><span class="pre">True</span></code> 的聚合函数中得到支持。</p>
<p><code class="docutils literal notranslate"><span class="pre">filter</span></code> 参数取一个 <a class="reference internal" href="querysets.html#django.db.models.Q" title="django.db.models.Q"><code class="xref py py-class docutils literal notranslate"><span class="pre">Q</span> <span class="pre">对象</span></code></a>，用于过滤被聚合的行。参见 <a class="reference internal" href="conditional-expressions.html#conditional-aggregation"><span class="std std-ref">条件聚合</span></a> 和 <a class="reference internal" href="../../topics/db/aggregation.html#filtering-on-annotations"><span class="std std-ref">Filtering on annotations</span></a> 的用法示例。</p>
<p><code class="docutils literal notranslate"><span class="pre">**extra</span></code> 关键字是 <code class="docutils literal notranslate"><span class="pre">key=value</span></code> 对，可以内插到 <code class="docutils literal notranslate"><span class="pre">template</span></code> 属性中。</p>
<div class="versionchanged">
<span class="title">Changed in Django 3.2:</span> <p>Support for transforms of the field was added.</p>
</div>
</div>
<div class="section" id="s-creating-your-own-aggregate-functions">
<span id="creating-your-own-aggregate-functions"></span><h3>创建你自己的聚合函数<a class="headerlink" href="#creating-your-own-aggregate-functions" title="永久链接至标题">¶</a></h3>
<p>你也可以创建自己的聚合函数。至少，你需要定义 <code class="docutils literal notranslate"><span class="pre">function</span></code>，但你也可以完全自定义生成的 SQL。下面是一个简单的例子：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Aggregate</span>

<span class="k">class</span> <span class="nc">Sum</span><span class="p">(</span><span class="n">Aggregate</span><span class="p">):</span>
    <span class="c1"># Supports SUM(ALL field).</span>
    <span class="n">function</span> <span class="o">=</span> <span class="s1">&#39;SUM&#39;</span>
    <span class="n">template</span> <span class="o">=</span> <span class="s1">&#39;</span><span class="si">%(function)s</span><span class="s1">(</span><span class="si">%(all_values)s%(expressions)s</span><span class="s1">)&#39;</span>
    <span class="n">allow_distinct</span> <span class="o">=</span> <span class="kc">False</span>

    <span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">expression</span><span class="p">,</span> <span class="n">all_values</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="o">**</span><span class="n">extra</span><span class="p">):</span>
        <span class="nb">super</span><span class="p">()</span><span class="o">.</span><span class="fm">__init__</span><span class="p">(</span>
            <span class="n">expression</span><span class="p">,</span>
            <span class="n">all_values</span><span class="o">=</span><span class="s1">&#39;ALL &#39;</span> <span class="k">if</span> <span class="n">all_values</span> <span class="k">else</span> <span class="s1">&#39;&#39;</span><span class="p">,</span>
            <span class="o">**</span><span class="n">extra</span>
        <span class="p">)</span>
</pre></div>
</div>
</div>
<div class="section" id="s-value-expressions">
<span id="value-expressions"></span><h3><code class="docutils literal notranslate"><span class="pre">Value()</span></code> 表达式<a class="headerlink" href="#value-expressions" title="永久链接至标题">¶</a></h3>
<dl class="class">
<dt id="django.db.models.Value">
<em class="property">class </em><code class="descname">Value</code>(<em>value</em>, <em>output_field=None</em>)<a class="headerlink" href="#django.db.models.Value" title="永久链接至目标">¶</a></dt>
<dd></dd></dl>

<p><code class="docutils literal notranslate"><span class="pre">Value()</span></code> 对象表示一个表达式中最小的成分：一个简单的值。当你需要在一个表达式中表示一个整数、布尔值或字符串的值时，你可以将该值包裹在一个 <code class="docutils literal notranslate"><span class="pre">Value()</span></code> 中。</p>
<p>你很少会需要直接使用 <code class="docutils literal notranslate"><span class="pre">Value()</span></code>。当你写下表达式 <code class="docutils literal notranslate"><span class="pre">F('field')</span> <span class="pre">+</span> <span class="pre">1</span></code> 时，Django 会隐式地将 <code class="docutils literal notranslate"><span class="pre">1</span></code> 包裹在 <code class="docutils literal notranslate"><span class="pre">Value()</span></code> 中，允许简单的值被用于更复杂的表达式中。当你想把一个字符串传递给一个表达式时，你需要使用 <code class="docutils literal notranslate"><span class="pre">Value()</span></code>。大多数表达式将字符串参数解释为字段的名称，如 <code class="docutils literal notranslate"><span class="pre">Lower('name')</span></code>。</p>
<p><code class="docutils literal notranslate"><span class="pre">value</span></code> 参数描述了要包含在表达式中的值，比如 <code class="docutils literal notranslate"><span class="pre">1</span></code>、<code class="docutils literal notranslate"><span class="pre">True</span></code> 或 <code class="docutils literal notranslate"><span class="pre">None</span></code>。Django 知道如何将这些 Python 值转换为相应的数据库类型。</p>
<p>The <code class="docutils literal notranslate"><span class="pre">output_field</span></code> argument should be a model field instance, like
<code class="docutils literal notranslate"><span class="pre">IntegerField()</span></code> or <code class="docutils literal notranslate"><span class="pre">BooleanField()</span></code>, into which Django will load the value
after it's retrieved from the database. Usually no arguments are needed when
instantiating the model field as any arguments relating to data validation
(<code class="docutils literal notranslate"><span class="pre">max_length</span></code>, <code class="docutils literal notranslate"><span class="pre">max_digits</span></code>, etc.) will not be enforced on the expression's
output value. If no <code class="docutils literal notranslate"><span class="pre">output_field</span></code> is specified it will be tentatively
inferred from the <a class="reference external" href="https://docs.python.org/3/library/functions.html#type" title="(在 Python v3.9)"><code class="xref py py-class docutils literal notranslate"><span class="pre">type</span></code></a> of the provided <code class="docutils literal notranslate"><span class="pre">value</span></code>, if possible. For
example, passing an instance of <a class="reference external" href="https://docs.python.org/3/library/datetime.html#datetime.datetime" title="(在 Python v3.9)"><code class="xref py py-class docutils literal notranslate"><span class="pre">datetime.datetime</span></code></a> as <code class="docutils literal notranslate"><span class="pre">value</span></code>
would default <code class="docutils literal notranslate"><span class="pre">output_field</span></code> to <a class="reference internal" href="fields.html#django.db.models.DateTimeField" title="django.db.models.DateTimeField"><code class="xref py py-class docutils literal notranslate"><span class="pre">DateTimeField</span></code></a>.</p>
<div class="versionchanged">
<span class="title">Changed in Django 3.2:</span> <p>Support for inferring a default <code class="docutils literal notranslate"><span class="pre">output_field</span></code> from the type of <code class="docutils literal notranslate"><span class="pre">value</span></code>
was added.</p>
</div>
</div>
<div class="section" id="s-expressionwrapper-expressions">
<span id="expressionwrapper-expressions"></span><h3><code class="docutils literal notranslate"><span class="pre">ExpressionWrapper()</span></code> 表达式<a class="headerlink" href="#expressionwrapper-expressions" title="永久链接至标题">¶</a></h3>
<dl class="class">
<dt id="django.db.models.ExpressionWrapper">
<em class="property">class </em><code class="descname">ExpressionWrapper</code>(<em>expression</em>, <em>output_field</em>)<a class="headerlink" href="#django.db.models.ExpressionWrapper" title="永久链接至目标">¶</a></dt>
<dd></dd></dl>

<p><code class="docutils literal notranslate"><span class="pre">ExpressionWrapper</span></code> 包裹另一个表达式，并提供对 <code class="docutils literal notranslate"><span class="pre">output_field</span></code> 等属性的访问，这些属性在其他表达式上可能无法使用。<code class="docutils literal notranslate"><span class="pre">ExpressionWrapper</span></code> 在对 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 具有不同类型的表达式使用算术时是必要的，如 <a class="reference internal" href="#using-f-with-annotations"><span class="std std-ref">与注解一起使用 F()</span></a> 中所述。</p>
</div>
<div class="section" id="s-conditional-expressions">
<span id="conditional-expressions"></span><h3>条件表达式<a class="headerlink" href="#conditional-expressions" title="永久链接至标题">¶</a></h3>
<p>条件表达式允许你在查询中使用 <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#if" title="(在 Python v3.9)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">if</span></code></a> ... <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#elif" title="(在 Python v3.9)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">elif</span></code></a> ... <a class="reference external" href="https://docs.python.org/3/reference/compound_stmts.html#else" title="(在 Python v3.9)"><code class="xref std std-keyword docutils literal notranslate"><span class="pre">else</span></code></a> 逻辑。Django 原生支持 SQL <code class="docutils literal notranslate"><span class="pre">CASE</span></code> 表达式。更多细节请参见 <a class="reference internal" href="conditional-expressions.html"><span class="doc">条件表达式</span></a>。</p>
</div>
<div class="section" id="s-subquery-expressions">
<span id="subquery-expressions"></span><h3><code class="docutils literal notranslate"><span class="pre">Subquery()</span></code> 表达式<a class="headerlink" href="#subquery-expressions" title="永久链接至标题">¶</a></h3>
<dl class="class">
<dt id="django.db.models.Subquery">
<em class="property">class </em><code class="descname">Subquery</code>(<em>queryset</em>, <em>output_field=None</em>)<a class="headerlink" href="#django.db.models.Subquery" title="永久链接至目标">¶</a></dt>
<dd></dd></dl>

<p>你可以使用 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 表达式向 <code class="docutils literal notranslate"><span class="pre">QuerySet</span></code> 添加一个显式子查询。</p>
<p>例如，在每篇文章中标注该文章最新评论的作者的电子邮件地址：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">OuterRef</span><span class="p">,</span> <span class="n">Subquery</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">newest</span> <span class="o">=</span> <span class="n">Comment</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">post</span><span class="o">=</span><span class="n">OuterRef</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s1">&#39;-created_at&#39;</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Post</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">newest_commenter_email</span><span class="o">=</span><span class="n">Subquery</span><span class="p">(</span><span class="n">newest</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s1">&#39;email&#39;</span><span class="p">)[:</span><span class="mi">1</span><span class="p">]))</span>
</pre></div>
</div>
<p>在 PostgreSQL 上，SQL 看起来像：</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="ss">&quot;post&quot;</span><span class="p">.</span><span class="ss">&quot;id&quot;</span><span class="p">,</span> <span class="p">(</span>
    <span class="k">SELECT</span> <span class="n">U0</span><span class="p">.</span><span class="ss">&quot;email&quot;</span>
    <span class="k">FROM</span> <span class="ss">&quot;comment&quot;</span> <span class="n">U0</span>
    <span class="k">WHERE</span> <span class="n">U0</span><span class="p">.</span><span class="ss">&quot;post_id&quot;</span> <span class="o">=</span> <span class="p">(</span><span class="ss">&quot;post&quot;</span><span class="p">.</span><span class="ss">&quot;id&quot;</span><span class="p">)</span>
    <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">U0</span><span class="p">.</span><span class="ss">&quot;created_at&quot;</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="mi">1</span>
<span class="p">)</span> <span class="k">AS</span> <span class="ss">&quot;newest_commenter_email&quot;</span> <span class="k">FROM</span> <span class="ss">&quot;post&quot;</span>
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">注解</p>
<p class="last">本节中的例子是为了展示如何强制 Django 执行一个子查询。在某些情况下，可以写一个等价的查询集，更清楚或更有效地执行同样的任务。</p>
</div>
<div class="section" id="s-referencing-columns-from-the-outer-queryset">
<span id="referencing-columns-from-the-outer-queryset"></span><h4>从外部查询集中引用列<a class="headerlink" href="#referencing-columns-from-the-outer-queryset" title="永久链接至标题">¶</a></h4>
<dl class="class">
<dt id="django.db.models.OuterRef">
<em class="property">class </em><code class="descname">OuterRef</code>(<em>field</em>)<a class="headerlink" href="#django.db.models.OuterRef" title="永久链接至目标">¶</a></dt>
<dd></dd></dl>

<p>Use <code class="docutils literal notranslate"><span class="pre">OuterRef</span></code> when a queryset in a <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> needs to refer to a field
from the outer query or its transform. It acts like an <a class="reference internal" href="#django.db.models.F" title="django.db.models.F"><code class="xref py py-class docutils literal notranslate"><span class="pre">F</span></code></a> expression
except that the check to see if it refers to a valid field isn't made until the
outer queryset is resolved.</p>
<p><code class="docutils literal notranslate"><span class="pre">OuterRef</span></code> 的实例可以与 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 的嵌套实例一起使用，以引用一个不是直接父级的包含查询集的实例。例如，这个查询集需要在一对嵌套的 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 实例中才能正确解析：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">Book</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">author</span><span class="o">=</span><span class="n">OuterRef</span><span class="p">(</span><span class="n">OuterRef</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">)))</span>
</pre></div>
</div>
<div class="versionchanged">
<span class="title">Changed in Django 3.2:</span> <p>Support for transforms of the field was added.</p>
</div>
</div>
<div class="section" id="s-limiting-a-subquery-to-a-single-column">
<span id="limiting-a-subquery-to-a-single-column"></span><h4>将子查询限制为单列<a class="headerlink" href="#limiting-a-subquery-to-a-single-column" title="永久链接至标题">¶</a></h4>
<p>有时必须从 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 中返回一列，例如，使用 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 作为 <code class="docutils literal notranslate"><span class="pre">__in</span></code> 查询的目标。要返回最近一天内发表的所有文章评论：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">datetime</span> <span class="kn">import</span> <span class="n">timedelta</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.utils</span> <span class="kn">import</span> <span class="n">timezone</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">one_day_ago</span> <span class="o">=</span> <span class="n">timezone</span><span class="o">.</span><span class="n">now</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">posts</span> <span class="o">=</span> <span class="n">Post</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">published_at__gte</span><span class="o">=</span><span class="n">one_day_ago</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Comment</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">post__in</span><span class="o">=</span><span class="n">Subquery</span><span class="p">(</span><span class="n">posts</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">)))</span>
</pre></div>
</div>
<p>在这种情况下，子查询必须使用 <a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.values" title="django.db.models.query.QuerySet.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">values()</span></code></a> 只返回一列：该文章的主键。</p>
</div>
<div class="section" id="s-limiting-the-subquery-to-a-single-row">
<span id="limiting-the-subquery-to-a-single-row"></span><h4>将子查询限制为单行<a class="headerlink" href="#limiting-the-subquery-to-a-single-row" title="永久链接至标题">¶</a></h4>
<p>为了防止子查询返回多条记录，使用了查询集的一个片断（<code class="docutils literal notranslate"><span class="pre">[:1]</span></code>）：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">subquery</span> <span class="o">=</span> <span class="n">Subquery</span><span class="p">(</span><span class="n">newest</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s1">&#39;email&#39;</span><span class="p">)[:</span><span class="mi">1</span><span class="p">])</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Post</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">newest_commenter_email</span><span class="o">=</span><span class="n">subquery</span><span class="p">)</span>
</pre></div>
</div>
<p>在这种情况下，子查询必须只返回一列 <em>和</em> 一行：最近创建的评论的电子邮件地址。</p>
<p>（使用 <a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.get" title="django.db.models.query.QuerySet.get"><code class="xref py py-meth docutils literal notranslate"><span class="pre">get()</span></code></a> 而不是分片会导致失败，因为 <code class="docutils literal notranslate"><span class="pre">OuterRef</span></code> 在 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 中使用查询集之前无法解析。)</p>
</div>
<div class="section" id="s-exists-subqueries">
<span id="exists-subqueries"></span><h4><code class="docutils literal notranslate"><span class="pre">Exists()</span></code> 子查询<a class="headerlink" href="#exists-subqueries" title="永久链接至标题">¶</a></h4>
<dl class="class">
<dt id="django.db.models.Exists">
<em class="property">class </em><code class="descname">Exists</code>(<em>queryset</em>)<a class="headerlink" href="#django.db.models.Exists" title="永久链接至目标">¶</a></dt>
<dd></dd></dl>

<p><code class="docutils literal notranslate"><span class="pre">Exists</span></code> 是一个 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 子类，它使用 SQL <code class="docutils literal notranslate"><span class="pre">EXISTS</span></code> 语句。在许多情况下，它的性能比子查询更好，因为当找到第一条匹配的记录时，数据库能够停止对子查询的执行。</p>
<p>例如，要对每篇文章进行注解，说明它是否有过去一天内的评论：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Exists</span><span class="p">,</span> <span class="n">OuterRef</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">datetime</span> <span class="kn">import</span> <span class="n">timedelta</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.utils</span> <span class="kn">import</span> <span class="n">timezone</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">one_day_ago</span> <span class="o">=</span> <span class="n">timezone</span><span class="o">.</span><span class="n">now</span><span class="p">()</span> <span class="o">-</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">recent_comments</span> <span class="o">=</span> <span class="n">Comment</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="gp">... </span>    <span class="n">post</span><span class="o">=</span><span class="n">OuterRef</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">),</span>
<span class="gp">... </span>    <span class="n">created_at__gte</span><span class="o">=</span><span class="n">one_day_ago</span><span class="p">,</span>
<span class="gp">... </span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Post</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">recent_comment</span><span class="o">=</span><span class="n">Exists</span><span class="p">(</span><span class="n">recent_comments</span><span class="p">))</span>
</pre></div>
</div>
<p>在 PostgreSQL 上，SQL 看起来像：</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="ss">&quot;post&quot;</span><span class="p">.</span><span class="ss">&quot;id&quot;</span><span class="p">,</span> <span class="ss">&quot;post&quot;</span><span class="p">.</span><span class="ss">&quot;published_at&quot;</span><span class="p">,</span> <span class="k">EXISTS</span><span class="p">(</span>
    <span class="k">SELECT</span> <span class="p">(</span><span class="mi">1</span><span class="p">)</span> <span class="k">as</span> <span class="ss">&quot;a&quot;</span>
    <span class="k">FROM</span> <span class="ss">&quot;comment&quot;</span> <span class="n">U0</span>
    <span class="k">WHERE</span> <span class="p">(</span>
        <span class="n">U0</span><span class="p">.</span><span class="ss">&quot;created_at&quot;</span> <span class="o">&gt;=</span> <span class="n">YYYY</span><span class="o">-</span><span class="n">MM</span><span class="o">-</span><span class="n">DD</span> <span class="n">HH</span><span class="p">:</span><span class="n">MM</span><span class="p">:</span><span class="n">SS</span> <span class="k">AND</span>
        <span class="n">U0</span><span class="p">.</span><span class="ss">&quot;post_id&quot;</span> <span class="o">=</span> <span class="ss">&quot;post&quot;</span><span class="p">.</span><span class="ss">&quot;id&quot;</span>
    <span class="p">)</span>
    <span class="k">LIMIT</span> <span class="mi">1</span>
<span class="p">)</span> <span class="k">AS</span> <span class="ss">&quot;recent_comment&quot;</span> <span class="k">FROM</span> <span class="ss">&quot;post&quot;</span>
</pre></div>
</div>
<p>没有必要强制 <code class="docutils literal notranslate"><span class="pre">Exists</span></code> 指向单一列，因为列会被丢弃，并返回一个布尔结果。同样，由于在 SQL <code class="docutils literal notranslate"><span class="pre">EXISTS</span></code> 子查询中，排序并不重要，只会降低性能，所以会自动删除。</p>
<p>可以用 <code class="docutils literal notranslate"><span class="pre">`~Exists()</span></code> 来查询 <code class="docutils literal notranslate"><span class="pre">NOT</span> <span class="pre">EXISTS</span></code>。</p>
</div>
<div class="section" id="s-filtering-on-a-subquery-or-exists-expressions">
<span id="filtering-on-a-subquery-or-exists-expressions"></span><h4>对 <code class="docutils literal notranslate"><span class="pre">Subquery()</span></code> 或 <code class="docutils literal notranslate"><span class="pre">Exists()</span></code> 表达式进行过滤。<a class="headerlink" href="#filtering-on-a-subquery-or-exists-expressions" title="永久链接至标题">¶</a></h4>
<p>在 <a class="reference internal" href="conditional-expressions.html#django.db.models.expressions.When" title="django.db.models.expressions.When"><code class="xref py py-class docutils literal notranslate"><span class="pre">When</span></code></a> 表达式中，返回布尔值的 <code class="docutils literal notranslate"><span class="pre">Subquery()</span></code> 和 <code class="docutils literal notranslate"><span class="pre">Exists()</span></code> 可以作为 <code class="docutils literal notranslate"><span class="pre">condition</span></code>，或者直接过滤一个查询集：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">recent_comments</span> <span class="o">=</span> <span class="n">Comment</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="o">...</span><span class="p">)</span>  <span class="c1"># From above</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Post</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Exists</span><span class="p">(</span><span class="n">recent_comments</span><span class="p">))</span>
</pre></div>
</div>
<p>这将确保子查询不会被添加到 <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> 列中，这可能会带来更好的性能。</p>
</div>
<div class="section" id="s-using-aggregates-within-a-subquery-expression">
<span id="using-aggregates-within-a-subquery-expression"></span><h4>在 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 表达式中使用集合。<a class="headerlink" href="#using-aggregates-within-a-subquery-expression" title="永久链接至标题">¶</a></h4>
<p>聚合可以在 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 中使用，但需要将 <a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.filter" title="django.db.models.query.QuerySet.filter"><code class="xref py py-meth docutils literal notranslate"><span class="pre">filter()</span></code></a>、<a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.values" title="django.db.models.query.QuerySet.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">values()</span></code></a> 和 <a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.annotate" title="django.db.models.query.QuerySet.annotate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">annotate()</span></code></a> 进行特定的组合，才能使子查询分组正确。</p>
<p>假设两种模式都有 <code class="docutils literal notranslate"><span class="pre">length</span></code> 字段，要找到帖子长度大于所有合计评论总长度的帖子：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">OuterRef</span><span class="p">,</span> <span class="n">Subquery</span><span class="p">,</span> <span class="n">Sum</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">comments</span> <span class="o">=</span> <span class="n">Comment</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">post</span><span class="o">=</span><span class="n">OuterRef</span><span class="p">(</span><span class="s1">&#39;pk&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">order_by</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s1">&#39;post&#39;</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">total_comments</span> <span class="o">=</span> <span class="n">comments</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">total</span><span class="o">=</span><span class="n">Sum</span><span class="p">(</span><span class="s1">&#39;length&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="s1">&#39;total&#39;</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Post</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">length__gt</span><span class="o">=</span><span class="n">Subquery</span><span class="p">(</span><span class="n">total_comments</span><span class="p">))</span>
</pre></div>
</div>
<p>初始的 <code class="docutils literal notranslate"><span class="pre">filter(...)</span></code> 将子查询限制在相关参数上。<code class="docutils literal notranslate"><span class="pre">order_by()</span></code> 删除 <code class="docutils literal notranslate"><span class="pre">Comment</span></code> 模型上的默认 <code class="xref py py-attr docutils literal notranslate"><span class="pre">Order</span></code> （如果有的话）。<code class="docutils literal notranslate"><span class="pre">values('post')</span></code> 按 <code class="docutils literal notranslate"><span class="pre">Post</span></code> 聚合评论。最后，<code class="docutils literal notranslate"><span class="pre">annotate(...)</span></code> 执行聚合。这些查询集方法的应用顺序很重要。在这种情况下，由于子查询必须限于一列，所以需要使用 <code class="docutils literal notranslate"><span class="pre">values('total')</span></code>。</p>
<p>这是在 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 内进行聚合的唯一方法，因为使用 <code class="xref py py-meth docutils literal notranslate"><span class="pre">aggregary()</span></code> 试图执行查询集（如果有 <code class="docutils literal notranslate"><span class="pre">OuterRef</span></code>，将无法解决）。</p>
</div>
</div>
<div class="section" id="s-raw-sql-expressions">
<span id="raw-sql-expressions"></span><h3>原始 SQL 表达式<a class="headerlink" href="#raw-sql-expressions" title="永久链接至标题">¶</a></h3>
<dl class="class">
<dt id="django.db.models.expressions.RawSQL">
<em class="property">class </em><code class="descname">RawSQL</code>(<em>sql</em>, <em>params</em>, <em>output_field=None</em>)<a class="headerlink" href="#django.db.models.expressions.RawSQL" title="永久链接至目标">¶</a></dt>
<dd></dd></dl>

<p>有时，数据库表达式不容易表达一个复杂的 <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> 子句。在这些边缘情况下，使用 <code class="docutils literal notranslate"><span class="pre">RawSQL</span></code> 表达式。例如：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models.expressions</span> <span class="kn">import</span> <span class="n">RawSQL</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">queryset</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span><span class="n">val</span><span class="o">=</span><span class="n">RawSQL</span><span class="p">(</span><span class="s2">&quot;select col from sometable where othercol = </span><span class="si">%s</span><span class="s2">&quot;</span><span class="p">,</span> <span class="p">(</span><span class="n">param</span><span class="p">,)))</span>
</pre></div>
</div>
<p>这些额外的查找可能无法移植到不同的数据库引擎中（因为你是显式地编写 SQL 代码），并且违反了 DRY 原则，所以你应该尽可能地避免它们。</p>
<p><code class="docutils literal notranslate"><span class="pre">RawSQL</span></code> expressions can also be used as the target of <code class="docutils literal notranslate"><span class="pre">__in</span></code> filters:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">queryset</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">id__in</span><span class="o">=</span><span class="n">RawSQL</span><span class="p">(</span><span class="s2">&quot;select id from sometable where col = </span><span class="si">%s</span><span class="s2">&quot;</span><span class="p">,</span> <span class="p">(</span><span class="n">param</span><span class="p">,)))</span>
</pre></div>
</div>
<div class="admonition warning">
<p class="first admonition-title">警告</p>
<p>为了防止 <a class="reference external" href="https://en.wikipedia.org/wiki/SQL_injection">SQL 注入攻击</a> ，你必须使用 <code class="docutils literal notranslate"><span class="pre">params</span></code> 来转义任何用户可以控制的参数。<code class="docutils literal notranslate"><span class="pre">params</span></code> 是一个必要的参数，以迫使你承认你没有用用户提供的数据来插值你的 SQL。</p>
<p>你也不能在 SQL 字符串中引用占位符。这个例子由于在 <code class="docutils literal notranslate"><span class="pre">%s</span></code> 周围的引号而容易受到 SQL 注入：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">RawSQL</span><span class="p">(</span><span class="s2">&quot;select col from sometable where othercol = &#39;</span><span class="si">%s</span><span class="s2">&#39;&quot;</span><span class="p">)</span>  <span class="c1"># unsafe!</span>
</pre></div>
</div>
<p class="last">你可以阅读更多关于 Django 的 <a class="reference internal" href="../../topics/security.html#sql-injection-protection"><span class="std std-ref">SQL 注入保护</span></a> 的工作原理。</p>
</div>
</div>
<div class="section" id="s-window-functions">
<span id="window-functions"></span><h3>窗口函数<a class="headerlink" href="#window-functions" title="永久链接至标题">¶</a></h3>
<p>窗口函数提供了一种在分区上应用函数的方法。与一般的聚合函数不同，窗口函数对 <a class="reference internal" href="#window-frames"><span class="std std-ref">帧</span></a> 和分区进行操作，并计算每行的结果。</p>
<p>你可以在同一个查询中指定多个窗口，这在 Django ORM 中相当于在一个 <a class="reference internal" href="../../topics/db/aggregation.html"><span class="doc">QuerySet.annotate()</span></a> 调用中包含多个表达式。ORM 并没有利用命名窗口，而是将其作为所选列的一部分。</p>
<dl class="class">
<dt id="django.db.models.expressions.Window">
<em class="property">class </em><code class="descname">Window</code>(<em>expression</em>, <em>partition_by=None</em>, <em>order_by=None</em>, <em>frame=None</em>, <em>output_field=None</em>)<a class="headerlink" href="#django.db.models.expressions.Window" title="永久链接至目标">¶</a></dt>
<dd><dl class="attribute">
<dt id="django.db.models.expressions.Window.filterable">
<code class="descname">filterable</code><a class="headerlink" href="#django.db.models.expressions.Window.filterable" title="永久链接至目标">¶</a></dt>
<dd><p>默认值为 <code class="docutils literal notranslate"><span class="pre">False</span></code>。SQL 标准不允许在 <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> 子句中引用窗口函数，当构建 <code class="docutils literal notranslate"><span class="pre">QuerySet</span></code> 时，Django 会引发异常。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.expressions.Window.template">
<code class="descname">template</code><a class="headerlink" href="#django.db.models.expressions.Window.template" title="永久链接至目标">¶</a></dt>
<dd><p>默认为 <code class="docutils literal notranslate"><span class="pre">%(expression)sOVER</span> <span class="pre">(%(window)s)'</span></code>。如果只提供 <code class="docutils literal notranslate"><span class="pre">expression</span></code> 参数，窗口子句将是空白的。</p>
</dd></dl>

</dd></dl>

<p><code class="docutils literal notranslate"><span class="pre">Window</span></code> 类是 <code class="docutils literal notranslate"><span class="pre">OVER</span></code> 子句的主要表达式。</p>
<p><code class="docutils literal notranslate"><span class="pre">expression</span></code> 参数是一个 <a class="reference internal" href="database-functions.html#window-functions"><span class="std std-ref">窗口函数</span></a>，一个 <a class="reference internal" href="querysets.html#aggregation-functions"><span class="std std-ref">聚合函数</span></a>，或者一个与窗口子句兼容的表达式。</p>
<p><code class="docutils literal notranslate"><span class="pre">partition_by</span></code> 参数是一个表达式列表（列名应包在 <code class="docutils literal notranslate"><span class="pre">F</span></code> 对象中），用于控制行的分区。分区缩小了用于计算结果集的行的范围。</p>
<p><code class="docutils literal notranslate"><span class="pre">output_field</span></code> 是作为参数或通过表达式指定的。</p>
<p><code class="docutils literal notranslate"><span class="pre">order_by</span></code> 参数接受一个表达式序列，你可以在这个序列上调用 <a class="reference internal" href="#django.db.models.Expression.asc" title="django.db.models.Expression.asc"><code class="xref py py-meth docutils literal notranslate"><span class="pre">asc()</span></code></a> 和 <code class="xref py py-meth docutils literal notranslate"><span class="pre">esc()</span></code>。顺序控制了应用表达式的顺序。例如，如果在一个分区的行上求和，第一个结果是第一行的值，第二个结果是第一行和第二行的和。</p>
<p><code class="docutils literal notranslate"><span class="pre">frame</span></code> 参数指定在计算中应该使用哪些其他行。详见 <a class="reference internal" href="#window-frames"><span class="std std-ref">帧</span></a>。</p>
<p>例如，用同一制片厂在同一类型和发行年份的电影的平均评分来注解每部电影：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Avg</span><span class="p">,</span> <span class="n">F</span><span class="p">,</span> <span class="n">Window</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models.functions</span> <span class="kn">import</span> <span class="n">ExtractYear</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Movie</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="n">avg_rating</span><span class="o">=</span><span class="n">Window</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">expression</span><span class="o">=</span><span class="n">Avg</span><span class="p">(</span><span class="s1">&#39;rating&#39;</span><span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">partition_by</span><span class="o">=</span><span class="p">[</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;studio&#39;</span><span class="p">),</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;genre&#39;</span><span class="p">)],</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">order_by</span><span class="o">=</span><span class="n">ExtractYear</span><span class="p">(</span><span class="s1">&#39;released&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">asc</span><span class="p">(),</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span><span class="p">)</span>
</pre></div>
</div>
<p>这可以让你检查一部电影的评分是好是坏，与它的同行相比。</p>
<p>你可能希望在同一个窗口，即同一个分区和帧上应用多个表达式。例如，你可以修改前面的例子，通过在同一查询中使用三个窗口函数，也包括每部电影的组别（相同的工作室、类型和发布年份）中的最佳和最差评级。前面例子中的分区和排序被提取到一个字典中，以减少重复：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Avg</span><span class="p">,</span> <span class="n">F</span><span class="p">,</span> <span class="n">Max</span><span class="p">,</span> <span class="n">Min</span><span class="p">,</span> <span class="n">Window</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models.functions</span> <span class="kn">import</span> <span class="n">ExtractYear</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">window</span> <span class="o">=</span> <span class="p">{</span>
<span class="gp">&gt;&gt;&gt; </span>   <span class="s1">&#39;partition_by&#39;</span><span class="p">:</span> <span class="p">[</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;studio&#39;</span><span class="p">),</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;genre&#39;</span><span class="p">)],</span>
<span class="gp">&gt;&gt;&gt; </span>   <span class="s1">&#39;order_by&#39;</span><span class="p">:</span> <span class="n">ExtractYear</span><span class="p">(</span><span class="s1">&#39;released&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">asc</span><span class="p">(),</span>
<span class="gp">&gt;&gt;&gt; </span><span class="p">}</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Movie</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="n">avg_rating</span><span class="o">=</span><span class="n">Window</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">expression</span><span class="o">=</span><span class="n">Avg</span><span class="p">(</span><span class="s1">&#39;rating&#39;</span><span class="p">),</span> <span class="o">**</span><span class="n">window</span><span class="p">,</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="n">best</span><span class="o">=</span><span class="n">Window</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">expression</span><span class="o">=</span><span class="n">Max</span><span class="p">(</span><span class="s1">&#39;rating&#39;</span><span class="p">),</span> <span class="o">**</span><span class="n">window</span><span class="p">,</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="n">worst</span><span class="o">=</span><span class="n">Window</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">expression</span><span class="o">=</span><span class="n">Min</span><span class="p">(</span><span class="s1">&#39;rating&#39;</span><span class="p">),</span> <span class="o">**</span><span class="n">window</span><span class="p">,</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span><span class="p">)</span>
</pre></div>
</div>
<p>在 Django 内置的数据库后端中，MySQL 8.0.2+、PostgreSQL、Oracle 都支持窗口表达式。不同的数据库对不同窗口表达式功能的支持也不同。例如， <a class="reference internal" href="#django.db.models.Expression.asc" title="django.db.models.Expression.asc"><code class="xref py py-meth docutils literal notranslate"><span class="pre">asc()</span></code></a> 和 <code class="xref py py-meth docutils literal notranslate"><span class="pre">esc()</span></code> 中的选项可能不被支持。根据需要，请查阅你的数据库的文档。</p>
<div class="section" id="s-frames">
<span id="s-window-frames"></span><span id="frames"></span><span id="window-frames"></span><h4>帧<a class="headerlink" href="#frames" title="永久链接至标题">¶</a></h4>
<p>对于一个窗口帧，你可以选择基于范围的行序列或普通的行序列。</p>
<dl class="class">
<dt id="django.db.models.expressions.ValueRange">
<em class="property">class </em><code class="descname">ValueRange</code>(<em>start=None</em>, <em>end=None</em>)<a class="headerlink" href="#django.db.models.expressions.ValueRange" title="永久链接至目标">¶</a></dt>
<dd><dl class="attribute">
<dt id="django.db.models.expressions.ValueRange.frame_type">
<code class="descname">frame_type</code><a class="headerlink" href="#django.db.models.expressions.ValueRange.frame_type" title="永久链接至目标">¶</a></dt>
<dd><p>该属性被设置为 <code class="docutils literal notranslate"><span class="pre">'RANGE'</span></code>。</p>
</dd></dl>

<p>PostgreSQL 对 <code class="docutils literal notranslate"><span class="pre">ValueRange</span></code> 的支持有限，只支持使用标准的开始和结束点，如 <code class="docutils literal notranslate"><span class="pre">CURRENT</span> <span class="pre">ROW</span></code> 和 <code class="docutils literal notranslate"><span class="pre">UNBOUNDED</span> <span class="pre">FOLLOWING</span></code>。</p>
</dd></dl>

<dl class="class">
<dt id="django.db.models.expressions.RowRange">
<em class="property">class </em><code class="descname">RowRange</code>(<em>start=None</em>, <em>end=None</em>)<a class="headerlink" href="#django.db.models.expressions.RowRange" title="永久链接至目标">¶</a></dt>
<dd><dl class="attribute">
<dt id="django.db.models.expressions.RowRange.frame_type">
<code class="descname">frame_type</code><a class="headerlink" href="#django.db.models.expressions.RowRange.frame_type" title="永久链接至目标">¶</a></dt>
<dd><p>该属性被设置为 <code class="docutils literal notranslate"><span class="pre">'ROWS'</span></code>。</p>
</dd></dl>

</dd></dl>

<p>两个类都返回 SQL，模板为：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">%</span><span class="p">(</span><span class="n">frame_type</span><span class="p">)</span><span class="n">s</span> <span class="n">BETWEEN</span> <span class="o">%</span><span class="p">(</span><span class="n">start</span><span class="p">)</span><span class="n">s</span> <span class="n">AND</span> <span class="o">%</span><span class="p">(</span><span class="n">end</span><span class="p">)</span><span class="n">s</span>
</pre></div>
</div>
<p>帧缩小了用于计算结果的行。它们从某个起点移动到某个指定的终点。帧可以不分区使用，但通常情况下，指定窗口的排序以确保结果的确定性是个好主意。在帧中，帧中的对等值是指具有等值的行，如果不存在排序子句，则是指所有行。</p>
<p>一个帧的默认起点是 <code class="docutils literal notranslate"><span class="pre">UNBOUNDED</span> <span class="pre">PRECEDING</span></code>，即分区的第一行。终点总是显式地包含在 ORM 生成的 SQL 中，默认为 <code class="docutils literal notranslate"><span class="pre">UNBOUNDED</span> <span class="pre">FOLLOWING</span></code>。默认帧包括从分区到集合中最后一行的所有行。</p>
<p><code class="docutils literal notranslate"><span class="pre">start</span></code> 和 <code class="docutils literal notranslate"><span class="pre">end</span></code> 参数的可接受值是 <code class="docutils literal notranslate"><span class="pre">None</span></code>、一个整数或零。<code class="docutils literal notranslate"><span class="pre">start</span></code> 的负整数会导致 <code class="docutils literal notranslate"><span class="pre">N</span> <span class="pre">preceding</span></code>，而 <code class="docutils literal notranslate"><span class="pre">None</span></code> 会产生 <code class="docutils literal notranslate"><span class="pre">UNBOUNDED</span> <span class="pre">PRECEDING</span></code>。对于 <code class="docutils literal notranslate"><span class="pre">start</span></code> 和 <code class="docutils literal notranslate"><span class="pre">end</span></code>，0 将返回 <code class="docutils literal notranslate"><span class="pre">CURRENT</span> <span class="pre">ROW</span></code>。<code class="docutils literal notranslate"><span class="pre">end</span></code> 接受正整数。</p>
<p><code class="docutils literal notranslate"><span class="pre">CURRENT</span> <span class="pre">ROW</span></code> 包括的内容有区别。当在 <code class="docutils literal notranslate"><span class="pre">ROWS</span></code> 模式下指定时，帧以当前行开始或结束。当在 <code class="docutils literal notranslate"><span class="pre">RANGE</span></code> 模式下指定时，根据排序子句，帧以第一个或最后一个对等值开始或结束。因此，<code class="docutils literal notranslate"><span class="pre">RANGE</span> <span class="pre">CURRENT</span> <span class="pre">ROW</span></code> 对具有由排序指定的相同值的行执行表达式。因为模板包括 <code class="docutils literal notranslate"><span class="pre">start</span></code> 和 <code class="docutils literal notranslate"><span class="pre">end</span></code> 点，所以可以用：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">ValueRange</span><span class="p">(</span><span class="n">start</span><span class="o">=</span><span class="mi">0</span><span class="p">,</span> <span class="n">end</span><span class="o">=</span><span class="mi">0</span><span class="p">)</span>
</pre></div>
</div>
<p>如果一部电影的&quot;对等值&quot;被描述为同一制片厂在同一年发行的同一类型的电影，那么这个 <code class="docutils literal notranslate"><span class="pre">RowRange</span></code> 的例子用一部电影的前后两部同行的平均评分来注解每部电影：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Avg</span><span class="p">,</span> <span class="n">F</span><span class="p">,</span> <span class="n">RowRange</span><span class="p">,</span> <span class="n">Window</span>
<span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models.functions</span> <span class="kn">import</span> <span class="n">ExtractYear</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Movie</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="n">avg_rating</span><span class="o">=</span><span class="n">Window</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">expression</span><span class="o">=</span><span class="n">Avg</span><span class="p">(</span><span class="s1">&#39;rating&#39;</span><span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">partition_by</span><span class="o">=</span><span class="p">[</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;studio&#39;</span><span class="p">),</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;genre&#39;</span><span class="p">)],</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">order_by</span><span class="o">=</span><span class="n">ExtractYear</span><span class="p">(</span><span class="s1">&#39;released&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">asc</span><span class="p">(),</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">frame</span><span class="o">=</span><span class="n">RowRange</span><span class="p">(</span><span class="n">start</span><span class="o">=-</span><span class="mi">2</span><span class="p">,</span> <span class="n">end</span><span class="o">=</span><span class="mi">2</span><span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span><span class="p">)</span>
</pre></div>
</div>
<p>如果数据库支持，可以根据分区中的表达式的值来指定开始和结束点。如果 <code class="docutils literal notranslate"><span class="pre">Movie</span></code> 模型中的 <code class="docutils literal notranslate"><span class="pre">released</span></code> 字段存储了每部电影的发布月份，那么这个 <code class="docutils literal notranslate"><span class="pre">ValueRange</span></code> 例子就用每部电影之前 12 个月到之后 12 个月之间发布的电影同行的平均评分来注释每部电影。</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Avg</span><span class="p">,</span> <span class="n">F</span><span class="p">,</span> <span class="n">ValueRange</span><span class="p">,</span> <span class="n">Window</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Movie</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="n">avg_rating</span><span class="o">=</span><span class="n">Window</span><span class="p">(</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">expression</span><span class="o">=</span><span class="n">Avg</span><span class="p">(</span><span class="s1">&#39;rating&#39;</span><span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">partition_by</span><span class="o">=</span><span class="p">[</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;studio&#39;</span><span class="p">),</span> <span class="n">F</span><span class="p">(</span><span class="s1">&#39;genre&#39;</span><span class="p">)],</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">order_by</span><span class="o">=</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;released&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">asc</span><span class="p">(),</span>
<span class="gp">&gt;&gt;&gt; </span>        <span class="n">frame</span><span class="o">=</span><span class="n">ValueRange</span><span class="p">(</span><span class="n">start</span><span class="o">=-</span><span class="mi">12</span><span class="p">,</span> <span class="n">end</span><span class="o">=</span><span class="mi">12</span><span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span>    <span class="p">),</span>
<span class="gp">&gt;&gt;&gt; </span><span class="p">)</span>
</pre></div>
</div>
</div>
</div>
</div>
<div class="section" id="s-technical-information">
<span id="technical-information"></span><h2>技术信息<a class="headerlink" href="#technical-information" title="永久链接至标题">¶</a></h2>
<p>下面你会发现对库作者可能有用的技术实现细节。下面的技术 API 和示例将有助于创建通用的查询表达式，可以扩展 Django 提供的内置功能。</p>
<div class="section" id="s-expression-api">
<span id="expression-api"></span><h3>表达式 API<a class="headerlink" href="#expression-api" title="永久链接至标题">¶</a></h3>
<p>查询表达式实现了 <a class="reference internal" href="lookups.html#query-expression"><span class="std std-ref">查询表达式 API</span></a>，但也暴露了下面列出的一些额外的方法和属性。所有查询表达式必须继承于 <code class="docutils literal notranslate"><span class="pre">Expression()</span></code> 或相关子类。</p>
<p>当一个查询表达式包装另一个表达式时，它负责在被包装的表达式上调用相应的方法。</p>
<dl class="class">
<dt id="django.db.models.Expression">
<em class="property">class </em><code class="descname">Expression</code><a class="headerlink" href="#django.db.models.Expression" title="永久链接至目标">¶</a></dt>
<dd><dl class="attribute">
<dt id="django.db.models.Expression.contains_aggregate">
<code class="descname">contains_aggregate</code><a class="headerlink" href="#django.db.models.Expression.contains_aggregate" title="永久链接至目标">¶</a></dt>
<dd><p>告诉 Django 这个表达式包含一个集合，需要在查询中添加一个 <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> 子句。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Expression.contains_over_clause">
<code class="descname">contains_over_clause</code><a class="headerlink" href="#django.db.models.Expression.contains_over_clause" title="永久链接至目标">¶</a></dt>
<dd><p>告诉 Django 这个表达式包含一个 <a class="reference internal" href="#django.db.models.expressions.Window" title="django.db.models.expressions.Window"><code class="xref py py-class docutils literal notranslate"><span class="pre">Window</span></code></a> 表达式。例如，它用于在修改数据的查询中不允许使用窗口函数表达式。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Expression.filterable">
<code class="descname">filterable</code><a class="headerlink" href="#django.db.models.Expression.filterable" title="永久链接至目标">¶</a></dt>
<dd><p>告诉 Django 这个表达式可以在 <a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.filter" title="django.db.models.query.QuerySet.filter"><code class="xref py py-meth docutils literal notranslate"><span class="pre">QuerySet.filter()</span></code></a> 中引用。默认值为 <code class="docutils literal notranslate"><span class="pre">True</span></code>。</p>
</dd></dl>

<dl class="attribute">
<dt id="django.db.models.Expression.window_compatible">
<code class="descname">window_compatible</code><a class="headerlink" href="#django.db.models.Expression.window_compatible" title="永久链接至目标">¶</a></dt>
<dd><p>告诉 Django 这个表达式可以作为 <a class="reference internal" href="#django.db.models.expressions.Window" title="django.db.models.expressions.Window"><code class="xref py py-class docutils literal notranslate"><span class="pre">Window</span></code></a> 的源表达式。默认值为 <code class="docutils literal notranslate"><span class="pre">False</span></code>。</p>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.resolve_expression">
<code class="descname">resolve_expression</code>(<em>query=None</em>, <em>allow_joins=True</em>, <em>reuse=None</em>, <em>summarize=False</em>, <em>for_save=False</em>)<a class="headerlink" href="#django.db.models.Expression.resolve_expression" title="永久链接至目标">¶</a></dt>
<dd><p>提供了在将表达式添加到查询之前对其进行任何预处理或验证的机会。<code class="docutils literal notranslate"><span class="pre">resolve_expression()</span></code> 也必须对任何嵌套的表达式调用。<code class="docutils literal notranslate"><span class="pre">copy()</span></code> 应该返回一个 <code class="docutils literal notranslate"><span class="pre">self</span></code> 的 <code class="docutils literal notranslate"><span class="pre">copy()`</span></code>，并进行必要的转换。</p>
<p><code class="docutils literal notranslate"><span class="pre">query</span></code> 是后端查询的实现。</p>
<p><code class="docutils literal notranslate"><span class="pre">allow_joins</span></code> 是一个允许或拒绝在查询中使用连接的布尔值。</p>
<p><code class="docutils literal notranslate"><span class="pre">reuse</span></code> 是一组可重用的多连接方案的连接。</p>
<p><code class="docutils literal notranslate"><span class="pre">summarize</span></code> 是一个布尔值，当 <code class="docutils literal notranslate"><span class="pre">True</span></code> 时，表示正在计算的查询是一个终端聚合查询。</p>
<p><code class="docutils literal notranslate"><span class="pre">for_save</span></code> 是一个布尔值，当 <code class="docutils literal notranslate"><span class="pre">True</span></code> 时，表示正在执行的查询正在进行创建或更新。</p>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.get_source_expressions">
<code class="descname">get_source_expressions</code>()<a class="headerlink" href="#django.db.models.Expression.get_source_expressions" title="永久链接至目标">¶</a></dt>
<dd><p>返回内部表达式的有序列表。例如：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">Sum</span><span class="p">(</span><span class="n">F</span><span class="p">(</span><span class="s1">&#39;foo&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">get_source_expressions</span><span class="p">()</span>
<span class="go">[F(&#39;foo&#39;)]</span>
</pre></div>
</div>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.set_source_expressions">
<code class="descname">set_source_expressions</code>(<em>expressions</em>)<a class="headerlink" href="#django.db.models.Expression.set_source_expressions" title="永久链接至目标">¶</a></dt>
<dd><p>获取一个表达式列表，并将其存储起来，使 <code class="docutils literal notranslate"><span class="pre">get_source_expressions()</span></code> 能够返回它们。</p>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.relabeled_clone">
<code class="descname">relabeled_clone</code>(<em>change_map</em>)<a class="headerlink" href="#django.db.models.Expression.relabeled_clone" title="永久链接至目标">¶</a></dt>
<dd><p>返回 <code class="docutils literal notranslate"><span class="pre">self`</span></code> 的克隆（副本），并重新标明所有列别名。当创建子查询时，列别名会被重新命名。<code class="docutils literal notranslate"><span class="pre">relabeled_clone()</span></code> 也应该对任何嵌套的表达式进行调用并分配给克隆。</p>
<p><code class="docutils literal notranslate"><span class="pre">change_map</span></code> 是一个将旧别名映射到新别名的字典。</p>
<p>举例：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">relabeled_clone</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">change_map</span><span class="p">):</span>
    <span class="n">clone</span> <span class="o">=</span> <span class="n">copy</span><span class="o">.</span><span class="n">copy</span><span class="p">(</span><span class="bp">self</span><span class="p">)</span>
    <span class="n">clone</span><span class="o">.</span><span class="n">expression</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">relabeled_clone</span><span class="p">(</span><span class="n">change_map</span><span class="p">)</span>
    <span class="k">return</span> <span class="n">clone</span>
</pre></div>
</div>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.convert_value">
<code class="descname">convert_value</code>(<em>value</em>, <em>expression</em>, <em>connection</em>)<a class="headerlink" href="#django.db.models.Expression.convert_value" title="永久链接至目标">¶</a></dt>
<dd><p>一个钩子，允许表达式将 <code class="docutils literal notranslate"><span class="pre">value`</span></code> 强制转换成一个更合适的类型。</p>
<p><code class="docutils literal notranslate"><span class="pre">expression</span></code> 与 <code class="docutils literal notranslate"><span class="pre">self</span></code> 相同。</p>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.get_group_by_cols">
<code class="descname">get_group_by_cols</code>(<em>alias=None</em>)<a class="headerlink" href="#django.db.models.Expression.get_group_by_cols" title="永久链接至目标">¶</a></dt>
<dd><p>负责返回该表达式引用的列列表。<code class="docutils literal notranslate"><span class="pre">get_group_by_cols()</span></code> 应在任何嵌套的表达式上调用。<code class="docutils literal notranslate"><span class="pre">F()</span></code> 对象，尤其是持有列的引用。<code class="docutils literal notranslate"><span class="pre">alias</span></code> 参数将是 <code class="docutils literal notranslate"><span class="pre">None</span></code>，除非表达式已经被注解并用于分组。</p>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.asc">
<code class="descname">asc</code>(<em>nulls_first=False</em>, <em>nulls_last=False</em>)<a class="headerlink" href="#django.db.models.Expression.asc" title="永久链接至目标">¶</a></dt>
<dd><p>返回准备按升序排序的表达式。</p>
<p><code class="docutils literal notranslate"><span class="pre">nulls_first</span></code> 和 <code class="docutils literal notranslate"><span class="pre">nulls_last</span></code> 定义了如何对空值进行排序。参见 <a class="reference internal" href="#using-f-to-sort-null-values"><span class="std std-ref">使用 F() 对空值进行排序</span></a> 的用法示例。</p>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.desc">
<code class="descname">desc</code>(<em>nulls_first=False</em>, <em>nulls_last=False</em>)<a class="headerlink" href="#django.db.models.Expression.desc" title="永久链接至目标">¶</a></dt>
<dd><p>返回准备好降序排序的表达式。</p>
<p><code class="docutils literal notranslate"><span class="pre">nulls_first</span></code> 和 <code class="docutils literal notranslate"><span class="pre">nulls_last</span></code> 定义了如何对空值进行排序。参见 <a class="reference internal" href="#using-f-to-sort-null-values"><span class="std std-ref">使用 F() 对空值进行排序</span></a> 的用法示例。</p>
</dd></dl>

<dl class="method">
<dt id="django.db.models.Expression.reverse_ordering">
<code class="descname">reverse_ordering</code>()<a class="headerlink" href="#django.db.models.Expression.reverse_ordering" title="永久链接至目标">¶</a></dt>
<dd><p>返回 <code class="docutils literal notranslate"><span class="pre">self</span></code>，包括在 <code class="docutils literal notranslate"><span class="pre">order_by</span></code> 中对排序顺序进行的任何修改。例如，一个实现 <code class="docutils literal notranslate"><span class="pre">NULLS</span> <span class="pre">LAST</span></code> 的表达式将把它的值改为 <code class="docutils literal notranslate"><span class="pre">NULLS</span> <span class="pre">FIRST</span></code>。只有实现排序顺序的表达式，如 <code class="docutils literal notranslate"><span class="pre">OrderBy</span></code> 才需要修改。当 <a class="reference internal" href="querysets.html#django.db.models.query.QuerySet.reverse" title="django.db.models.query.QuerySet.reverse"><code class="xref py py-meth docutils literal notranslate"><span class="pre">reverse()</span></code></a> 在一个查询集上被调用时，会调用这个方法。</p>
</dd></dl>

</dd></dl>

</div>
<div class="section" id="s-writing-your-own-query-expressions">
<span id="writing-your-own-query-expressions"></span><h3>编写自己的查询表达式<a class="headerlink" href="#writing-your-own-query-expressions" title="永久链接至标题">¶</a></h3>
<p>你可以编写你自己的查询表达式类，这些类使用并可以与其他查询表达式集成。让我们通过一个例子，在不使用内置的 <a class="reference internal" href="#func-expressions"><span class="std std-ref">Func() 表达式</span></a> 的情况下，编写一个 <code class="docutils literal notranslate"><span class="pre">COALESCE</span></code> SQL 函数的实现。</p>
<p><code class="docutils literal notranslate"><span class="pre">COALESCE</span></code> SQL 函数被定义为接收一个列或值的列表，它将返回第一个不是 <code class="docutils literal notranslate"><span class="pre">NULL</span></code> 的列或值。它将返回第一个不是 <code class="docutils literal notranslate"><span class="pre">NULL</span></code> 的列或值。</p>
<p>我们将首先定义用于生成 SQL 的模板和一个 <code class="docutils literal notranslate"><span class="pre">__init__()</span></code> 方法来设置一些属性：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">copy</span>
<span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Expression</span>

<span class="k">class</span> <span class="nc">Coalesce</span><span class="p">(</span><span class="n">Expression</span><span class="p">):</span>
    <span class="n">template</span> <span class="o">=</span> <span class="s1">&#39;COALESCE( </span><span class="si">%(expressions)s</span><span class="s1"> )&#39;</span>

    <span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">expressions</span><span class="p">,</span> <span class="n">output_field</span><span class="p">):</span>
      <span class="nb">super</span><span class="p">()</span><span class="o">.</span><span class="fm">__init__</span><span class="p">(</span><span class="n">output_field</span><span class="o">=</span><span class="n">output_field</span><span class="p">)</span>
      <span class="k">if</span> <span class="nb">len</span><span class="p">(</span><span class="n">expressions</span><span class="p">)</span> <span class="o">&lt;</span> <span class="mi">2</span><span class="p">:</span>
          <span class="k">raise</span> <span class="ne">ValueError</span><span class="p">(</span><span class="s1">&#39;expressions must have at least 2 elements&#39;</span><span class="p">)</span>
      <span class="k">for</span> <span class="n">expression</span> <span class="ow">in</span> <span class="n">expressions</span><span class="p">:</span>
          <span class="k">if</span> <span class="ow">not</span> <span class="nb">hasattr</span><span class="p">(</span><span class="n">expression</span><span class="p">,</span> <span class="s1">&#39;resolve_expression&#39;</span><span class="p">):</span>
              <span class="k">raise</span> <span class="ne">TypeError</span><span class="p">(</span><span class="s1">&#39;</span><span class="si">%r</span><span class="s1"> is not an Expression&#39;</span> <span class="o">%</span> <span class="n">expression</span><span class="p">)</span>
      <span class="bp">self</span><span class="o">.</span><span class="n">expressions</span> <span class="o">=</span> <span class="n">expressions</span>
</pre></div>
</div>
<p>我们对参数进行一些基本的验证，包括要求至少有 2 个列或值，并确保它们是表达式。我们在这里要求 <code class="docutils literal notranslate"><span class="pre">output_field</span></code>，这样 Django 就知道要把最终的结果分配给什么样的模型字段。</p>
<p>现在我们实现预处理和验证。由于此时我们没有任何自己的验证，所以我们委托给嵌套的表达式：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">resolve_expression</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">query</span><span class="o">=</span><span class="kc">None</span><span class="p">,</span> <span class="n">allow_joins</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">reuse</span><span class="o">=</span><span class="kc">None</span><span class="p">,</span> <span class="n">summarize</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="n">for_save</span><span class="o">=</span><span class="kc">False</span><span class="p">):</span>
    <span class="n">c</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">copy</span><span class="p">()</span>
    <span class="n">c</span><span class="o">.</span><span class="n">is_summary</span> <span class="o">=</span> <span class="n">summarize</span>
    <span class="k">for</span> <span class="n">pos</span><span class="p">,</span> <span class="n">expression</span> <span class="ow">in</span> <span class="nb">enumerate</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">expressions</span><span class="p">):</span>
        <span class="n">c</span><span class="o">.</span><span class="n">expressions</span><span class="p">[</span><span class="n">pos</span><span class="p">]</span> <span class="o">=</span> <span class="n">expression</span><span class="o">.</span><span class="n">resolve_expression</span><span class="p">(</span><span class="n">query</span><span class="p">,</span> <span class="n">allow_joins</span><span class="p">,</span> <span class="n">reuse</span><span class="p">,</span> <span class="n">summarize</span><span class="p">,</span> <span class="n">for_save</span><span class="p">)</span>
    <span class="k">return</span> <span class="n">c</span>
</pre></div>
</div>
<p>接下来，我们编写负责生成 SQL 的方法：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">as_sql</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="n">template</span><span class="o">=</span><span class="kc">None</span><span class="p">):</span>
    <span class="n">sql_expressions</span><span class="p">,</span> <span class="n">sql_params</span> <span class="o">=</span> <span class="p">[],</span> <span class="p">[]</span>
    <span class="k">for</span> <span class="n">expression</span> <span class="ow">in</span> <span class="bp">self</span><span class="o">.</span><span class="n">expressions</span><span class="p">:</span>
        <span class="n">sql</span><span class="p">,</span> <span class="n">params</span> <span class="o">=</span> <span class="n">compiler</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">expression</span><span class="p">)</span>
        <span class="n">sql_expressions</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">sql</span><span class="p">)</span>
        <span class="n">sql_params</span><span class="o">.</span><span class="n">extend</span><span class="p">(</span><span class="n">params</span><span class="p">)</span>
    <span class="n">template</span> <span class="o">=</span> <span class="n">template</span> <span class="ow">or</span> <span class="bp">self</span><span class="o">.</span><span class="n">template</span>
    <span class="n">data</span> <span class="o">=</span> <span class="p">{</span><span class="s1">&#39;expressions&#39;</span><span class="p">:</span> <span class="s1">&#39;,&#39;</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">sql_expressions</span><span class="p">)}</span>
    <span class="k">return</span> <span class="n">template</span> <span class="o">%</span> <span class="n">data</span><span class="p">,</span> <span class="n">sql_params</span>

<span class="k">def</span> <span class="nf">as_oracle</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">):</span>
    <span class="sd">&quot;&quot;&quot;</span>
<span class="sd">    Example of vendor specific handling (Oracle in this case).</span>
<span class="sd">    Let&#39;s make the function name lowercase.</span>
<span class="sd">    &quot;&quot;&quot;</span>
    <span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">as_sql</span><span class="p">(</span><span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="n">template</span><span class="o">=</span><span class="s1">&#39;coalesce( </span><span class="si">%(expressions)s</span><span class="s1"> )&#39;</span><span class="p">)</span>
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">as_sql()</span></code> 方法可以支持自定义关键字参数，允许 <code class="docutils literal notranslate"><span class="pre">as_vendorname()</span></code> 方法覆盖用于生成 SQL 字符串的数据。使用 <code class="docutils literal notranslate"><span class="pre">as_sql()</span></code> 关键字参数进行自定义最好是在 <code class="docutils literal notranslate"><span class="pre">as_vendorname()</span></code> 方法中突变 <code class="docutils literal notranslate"><span class="pre">self</span></code>，因为后者在不同的数据库后端运行时可能会导致错误。如果你的类依赖于类属性来定义数据，可以考虑在你的 <code class="docutils literal notranslate"><span class="pre">as_sql()</span></code> 方法中允许覆盖。</p>
<p>我们使用 <code class="docutils literal notranslate"><span class="pre">compiler.compile()</span></code> 方法为每个 <code class="docutils literal notranslate"><span class="pre">expressions</span></code> 生成 SQL，并将结果用逗号连接起来。然后在模板中填入我们的数据，并返回 SQL 和参数。</p>
<p>我们还定义了一个专门针对 Oracle 后端的自定义实现。如果使用 Oracle 后端，将调用 <code class="docutils literal notranslate"><span class="pre">as_oracle()</span></code> 函数，而不是 <code class="docutils literal notranslate"><span class="pre">as_sql()</span></code>。</p>
<p>最后，我们实现了其余的方法，使我们的查询表达式能够与其他查询表达式很好地配合：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">get_source_expressions</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
    <span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">expressions</span>

<span class="k">def</span> <span class="nf">set_source_expressions</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">expressions</span><span class="p">):</span>
    <span class="bp">self</span><span class="o">.</span><span class="n">expressions</span> <span class="o">=</span> <span class="n">expressions</span>
</pre></div>
</div>
<p>让我们来看看它如何工作：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">F</span><span class="p">,</span> <span class="n">Value</span><span class="p">,</span> <span class="n">CharField</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">qs</span> <span class="o">=</span> <span class="n">Company</span><span class="o">.</span><span class="n">objects</span><span class="o">.</span><span class="n">annotate</span><span class="p">(</span>
<span class="gp">... </span>   <span class="n">tagline</span><span class="o">=</span><span class="n">Coalesce</span><span class="p">([</span>
<span class="gp">... </span>       <span class="n">F</span><span class="p">(</span><span class="s1">&#39;motto&#39;</span><span class="p">),</span>
<span class="gp">... </span>       <span class="n">F</span><span class="p">(</span><span class="s1">&#39;ticker_name&#39;</span><span class="p">),</span>
<span class="gp">... </span>       <span class="n">F</span><span class="p">(</span><span class="s1">&#39;description&#39;</span><span class="p">),</span>
<span class="gp">... </span>       <span class="n">Value</span><span class="p">(</span><span class="s1">&#39;No Tagline&#39;</span><span class="p">)</span>
<span class="gp">... </span>       <span class="p">],</span> <span class="n">output_field</span><span class="o">=</span><span class="n">CharField</span><span class="p">()))</span>
<span class="gp">&gt;&gt;&gt; </span><span class="k">for</span> <span class="n">c</span> <span class="ow">in</span> <span class="n">qs</span><span class="p">:</span>
<span class="gp">... </span>    <span class="nb">print</span><span class="p">(</span><span class="s2">&quot;</span><span class="si">%s</span><span class="s2">: </span><span class="si">%s</span><span class="s2">&quot;</span> <span class="o">%</span> <span class="p">(</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">c</span><span class="o">.</span><span class="n">tagline</span><span class="p">))</span>
<span class="gp">...</span>
<span class="go">Google: Do No Evil</span>
<span class="go">Apple: AAPL</span>
<span class="go">Yahoo: Internet Company</span>
<span class="go">Django Software Foundation: No Tagline</span>
</pre></div>
</div>
<div class="section" id="s-avoiding-sql-injection">
<span id="s-avoiding-sql-injection-in-query-expressions"></span><span id="avoiding-sql-injection"></span><span id="avoiding-sql-injection-in-query-expressions"></span><h4>避免 SQL 注入<a class="headerlink" href="#avoiding-sql-injection" title="永久链接至标题">¶</a></h4>
<p>由于 <code class="docutils literal notranslate"><span class="pre">Func</span></code> 的关键字参数 <code class="docutils literal notranslate"><span class="pre">__init__()</span></code> （<code class="docutils literal notranslate"><span class="pre">**extra</span></code>）和 <code class="docutils literal notranslate"><span class="pre">as_sql()</span></code> （<code class="docutils literal notranslate"><span class="pre">**extra_context</span></code>）的参数是内插到 SQL 字符串中，而不是作为查询参数传递的（数据库驱动程序会对其进行转义处理），因此它们不能包含不受信任的用户输入。</p>
<p>例如，如果 <code class="docutils literal notranslate"><span class="pre">substring</span></code> 是用户提供的，这个函数就容易被 SQL 注入：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="kn">import</span> <span class="n">Func</span>

<span class="k">class</span> <span class="nc">Position</span><span class="p">(</span><span class="n">Func</span><span class="p">):</span>
    <span class="n">function</span> <span class="o">=</span> <span class="s1">&#39;POSITION&#39;</span>
    <span class="n">template</span> <span class="o">=</span> <span class="s2">&quot;</span><span class="si">%(function)s</span><span class="s2">(&#39;</span><span class="si">%(substring)s</span><span class="s2">&#39; in </span><span class="si">%(expressions)s</span><span class="s2">)&quot;</span>

    <span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">expression</span><span class="p">,</span> <span class="n">substring</span><span class="p">):</span>
        <span class="c1"># substring=substring is an SQL injection vulnerability!</span>
        <span class="nb">super</span><span class="p">()</span><span class="o">.</span><span class="fm">__init__</span><span class="p">(</span><span class="n">expression</span><span class="p">,</span> <span class="n">substring</span><span class="o">=</span><span class="n">substring</span><span class="p">)</span>
</pre></div>
</div>
<p>这个函数在没有任何参数的情况下生成一个 SQL 字符串。由于 <code class="docutils literal notranslate"><span class="pre">substring</span></code> 是作为关键字参数传递给 <code class="docutils literal notranslate"><span class="pre">super().__init__()</span></code> 的，所以在将查询发送到数据库之前，它就被插入到 SQL 字符串中。</p>
<p>下面是更正后的改写：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Position</span><span class="p">(</span><span class="n">Func</span><span class="p">):</span>
    <span class="n">function</span> <span class="o">=</span> <span class="s1">&#39;POSITION&#39;</span>
    <span class="n">arg_joiner</span> <span class="o">=</span> <span class="s1">&#39; IN &#39;</span>

    <span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">expression</span><span class="p">,</span> <span class="n">substring</span><span class="p">):</span>
        <span class="nb">super</span><span class="p">()</span><span class="o">.</span><span class="fm">__init__</span><span class="p">(</span><span class="n">substring</span><span class="p">,</span> <span class="n">expression</span><span class="p">)</span>
</pre></div>
</div>
<p>用 <code class="docutils literal notranslate"><span class="pre">substring</span></code> 代替作为位置参数传递，它将作为数据库查询的参数传递。</p>
</div>
</div>
<div class="section" id="s-adding-support-in-third-party-database-backends">
<span id="adding-support-in-third-party-database-backends"></span><h3>在第三方数据库后端增加支持<a class="headerlink" href="#adding-support-in-third-party-database-backends" title="永久链接至标题">¶</a></h3>
<p>如果你使用的数据库后端对某个函数使用了不同的 SQL 语法，你可以通过在函数的类上打一个新的方法来增加对它的支持。</p>
<p>比方说，我们正在为微软的 SQL Server 编写一个后端，它使用 SQL 的 <code class="docutils literal notranslate"><span class="pre">LEN</span></code> 而不是 <code class="docutils literal notranslate"><span class="pre">LENGTH</span></code> 来实现 <a class="reference internal" href="database-functions.html#django.db.models.functions.Length" title="django.db.models.functions.Length"><code class="xref py py-class docutils literal notranslate"><span class="pre">Length</span></code></a> 函数。我们将把一个名为 <code class="docutils literal notranslate"><span class="pre">as_sqlserver()</span></code> 的新方法移植到 <code class="docutils literal notranslate"><span class="pre">Length</span></code> 类上：</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models.functions</span> <span class="kn">import</span> <span class="n">Length</span>

<span class="k">def</span> <span class="nf">sqlserver_length</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">):</span>
    <span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">as_sql</span><span class="p">(</span><span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="n">function</span><span class="o">=</span><span class="s1">&#39;LEN&#39;</span><span class="p">)</span>

<span class="n">Length</span><span class="o">.</span><span class="n">as_sqlserver</span> <span class="o">=</span> <span class="n">sqlserver_length</span>
</pre></div>
</div>
<p>你也可以使用 <code class="docutils literal notranslate"><span class="pre">as_sql()</span></code> 的 <code class="docutils literal notranslate"><span class="pre">template</span></code> 参数自定义 SQL。</p>
<p>我们使用 <code class="docutils literal notranslate"><span class="pre">as_sqlserver()</span></code>，因为 <code class="docutils literal notranslate"><span class="pre">django.db.connection.vendor</span></code> 返回 <code class="docutils literal notranslate"><span class="pre">sqlserver</span></code> 作为后端。</p>
<p>第三方后端可以在后端包的顶层 <code class="docutils literal notranslate"><span class="pre">__init__.py</span></code> 文件或从顶层 <code class="docutils literal notranslate"><span class="pre">__init__.py</span></code> 导入的顶层 <code class="docutils literal notranslate"><span class="pre">expressions.py</span></code> 文件（或包）中注册它们的函数。</p>
<p>对于希望给自己正在使用的后端打补丁的用户项目来说，这段代码应该存在于 <code class="xref py py-meth docutils literal notranslate"><span class="pre">AppConfig.ready()</span></code> 方法中。</p>
</div>
</div>
</div>


          </div>
        </div>
      </div>
      
        
          <div class="yui-b" id="sidebar">
            
      <div class="sphinxsidebar" role="navigation" aria-label="main navigation">
        <div class="sphinxsidebarwrapper">
  <h3><a href="../../contents.html">Table of Contents</a></h3>
  <ul>
<li><a class="reference internal" href="#">查询表达式</a><ul>
<li><a class="reference internal" href="#supported-arithmetic">支持的算术</a></li>
<li><a class="reference internal" href="#some-examples">一些例子</a></li>
<li><a class="reference internal" href="#built-in-expressions">内置表达式</a><ul>
<li><a class="reference internal" href="#f-expressions"><code class="docutils literal notranslate"><span class="pre">F()</span></code> 表达式</a><ul>
<li><a class="reference internal" href="#avoiding-race-conditions-using-f">使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 避免竞争条件</a></li>
<li><a class="reference internal" href="#f-assignments-persist-after-model-save"><code class="docutils literal notranslate"><span class="pre">F()</span></code> 赋值在 <code class="docutils literal notranslate"><span class="pre">Model.save()</span></code> 之后持续存在</a></li>
<li><a class="reference internal" href="#using-f-in-filters">在过滤器中使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code></a></li>
<li><a class="reference internal" href="#using-f-with-annotations">与注解一起使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code></a></li>
<li><a class="reference internal" href="#using-f-to-sort-null-values">使用 <code class="docutils literal notranslate"><span class="pre">F()</span></code> 对空值进行排序</a></li>
</ul>
</li>
<li><a class="reference internal" href="#func-expressions"><code class="docutils literal notranslate"><span class="pre">Func()</span></code> 表达式</a></li>
<li><a class="reference internal" href="#aggregate-expressions"><code class="docutils literal notranslate"><span class="pre">Aggregate()</span></code> 表达式</a></li>
<li><a class="reference internal" href="#creating-your-own-aggregate-functions">创建你自己的聚合函数</a></li>
<li><a class="reference internal" href="#value-expressions"><code class="docutils literal notranslate"><span class="pre">Value()</span></code> 表达式</a></li>
<li><a class="reference internal" href="#expressionwrapper-expressions"><code class="docutils literal notranslate"><span class="pre">ExpressionWrapper()</span></code> 表达式</a></li>
<li><a class="reference internal" href="#conditional-expressions">条件表达式</a></li>
<li><a class="reference internal" href="#subquery-expressions"><code class="docutils literal notranslate"><span class="pre">Subquery()</span></code> 表达式</a><ul>
<li><a class="reference internal" href="#referencing-columns-from-the-outer-queryset">从外部查询集中引用列</a></li>
<li><a class="reference internal" href="#limiting-a-subquery-to-a-single-column">将子查询限制为单列</a></li>
<li><a class="reference internal" href="#limiting-the-subquery-to-a-single-row">将子查询限制为单行</a></li>
<li><a class="reference internal" href="#exists-subqueries"><code class="docutils literal notranslate"><span class="pre">Exists()</span></code> 子查询</a></li>
<li><a class="reference internal" href="#filtering-on-a-subquery-or-exists-expressions">对 <code class="docutils literal notranslate"><span class="pre">Subquery()</span></code> 或 <code class="docutils literal notranslate"><span class="pre">Exists()</span></code> 表达式进行过滤。</a></li>
<li><a class="reference internal" href="#using-aggregates-within-a-subquery-expression">在 <code class="docutils literal notranslate"><span class="pre">Subquery</span></code> 表达式中使用集合。</a></li>
</ul>
</li>
<li><a class="reference internal" href="#raw-sql-expressions">原始 SQL 表达式</a></li>
<li><a class="reference internal" href="#window-functions">窗口函数</a><ul>
<li><a class="reference internal" href="#frames">帧</a></li>
</ul>
</li>
</ul>
</li>
<li><a class="reference internal" href="#technical-information">技术信息</a><ul>
<li><a class="reference internal" href="#expression-api">表达式 API</a></li>
<li><a class="reference internal" href="#writing-your-own-query-expressions">编写自己的查询表达式</a><ul>
<li><a class="reference internal" href="#avoiding-sql-injection">避免 SQL 注入</a></li>
</ul>
</li>
<li><a class="reference internal" href="#adding-support-in-third-party-database-backends">在第三方数据库后端增加支持</a></li>
</ul>
</li>
</ul>
</li>
</ul>

  <h4>上一个主题</h4>
  <p class="topless"><a href="lookups.html"
                        title="上一章">查找 API 参考</a></p>
  <h4>下一个主题</h4>
  <p class="topless"><a href="conditional-expressions.html"
                        title="下一章">条件表达式</a></p>
  <div role="note" aria-label="source link">
    <h3>本页</h3>
    <ul class="this-page-menu">
      <li><a href="../../_sources/ref/models/expressions.txt"
            rel="nofollow">显示源代码</a></li>
    </ul>
   </div>
<div id="searchbox" style="display: none" role="search">
  <h3>快速搜索</h3>
    <div class="searchformwrapper">
    <form class="search" action="../../search.html" method="get">
      <input type="text" name="q" />
      <input type="submit" value="转向" />
      <input type="hidden" name="check_keywords" value="yes" />
      <input type="hidden" name="area" value="default" />
    </form>
    </div>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
        </div>
      </div>
              <h3>Last update:</h3>
              <p class="topless">7月 23, 2021</p>
          </div>
        
      
    </div>

    <div id="ft">
      <div class="nav">
    &laquo; <a href="lookups.html" title="查找 API 参考">previous</a>
     |
    <a href="../index.html" title="API 参考" accesskey="U">up</a>
   |
    <a href="conditional-expressions.html" title="条件表达式">next</a> &raquo;</div>
    </div>
  </div>

      <div class="clearer"></div>
    </div>
  </body>
</html>